DECLARE @TABLE NVARCHAR(50)='HESAPISL'
IF (SELECT COUNT(TABLOADI) FROM INDEXSIFIRLAMALOG WHERE TABLOADI=@TABLE)=0
BEGIN
	INSERT INTO INDEXSIFIRLAMALOG (TABLOADI) VALUES(@TABLE)
END
GO

DECLARE @TABLE NVARCHAR(50)='HESAPISL'
DECLARE @SIFIRLANDI BIT=0
SELECT @SIFIRLANDI=ISNULL(SIFIRLANDI,0) FROM INDEXSIFIRLAMALOG WHERE TABLOADI=@TABLE

DECLARE @TableName NVARCHAR(100),@IndexName NVARCHAR(500),@ColumnName NVARCHAR(100),@IsInclude BIT
DECLARE @DROPSQL NVARCHAR(3500)='',@INDEXS NVARCHAR(MAX)='',@INDEXCOLS NVARCHAR(MAX)='',@INDEXINCCOLS NVARCHAR(MAX)='',@IndexNameTut NVARCHAR(500)='' 
DECLARE CUR CURSOR FOR
SELECT t.name as TableName, ind.name as IndexName, col.name as ColumnName, ic.is_included_column as IsInclude
FROM sys.indexes ind 
INNER JOIN sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id 
INNER JOIN sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id 
INNER JOIN sys.tables t ON ind.object_id = t.object_id 
WHERE ind.is_primary_key = 0 AND ind.is_unique = 0 AND ind.is_unique_constraint = 0 AND t.is_ms_shipped = 0 AND ind.data_space_id=1 
AND t.name = REPLACE(REPLACE(@TABLE,'[',''),']','') AND (@SIFIRLANDI=0 OR (@SIFIRLANDI=1 AND LEFT(ind.name,3)='IX_' ))
ORDER BY t.name, ind.name, ind.index_id, ic.is_included_column, ic.key_ordinal

OPEN CUR
FETCH NEXT FROM CUR INTO @TableName,@IndexName,@ColumnName,@IsInclude
WHILE @@FETCH_STATUS = 0  
BEGIN
	if @IndexName<>@IndexNametut
	BEGIN
		IF @IndexNametut<>''
		BEGIN
			SET @INDEXS = @INDEXS + @IndexNametut+'('+@INDEXCOLS+')INCLUDE('+@INDEXINCCOLS+')   '
			SET @INDEXCOLS=''
			SET @INDEXINCCOLS=''
			SET @DROPSQL = @DROPSQL+'DROP INDEX ['+@IndexNametut+'] ON '+@TABLE+';'			
		END
		SET @IndexNametut = @IndexName
	END

	IF @IsInclude=1 
		IF @INDEXINCCOLS='' SET @INDEXINCCOLS=@INDEXINCCOLS+@ColumnName
		ELSE SET @INDEXINCCOLS=@INDEXINCCOLS+','+@ColumnName
	ELSE
		IF @INDEXCOLS='' SET @INDEXCOLS=@INDEXINCCOLS+@ColumnName
		ELSE SET @INDEXCOLS=@INDEXCOLS+','+@ColumnName
	FETCH NEXT FROM CUR INTO @TableName,@IndexName,@ColumnName,@IsInclude
END
CLOSE CUR;  
DEALLOCATE CUR;

IF @IndexNametut<>'' SET @INDEXS = @INDEXS + @IndexNametut+'('+@INDEXCOLS+')INCLUDE('+@INDEXINCCOLS+')   '
IF @IndexNametut<>'' SET @DROPSQL = @DROPSQL+'DROP INDEX ['+@IndexNametut+'] ON '+@TABLE+';'

IF @SIFIRLANDI=0
	UPDATE INDEXSIFIRLAMALOG SET SIFIRLANDI=1,ESKIINDEXLER=@INDEXS WHERE TABLOADI=@TABLE

IF @DROPSQL<>'' EXEC(@DROPSQL)
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[HESAPISL]') AND name = N'IX_HESAPISL_HSTODEMEID')
	CREATE NONCLUSTERED INDEX [IX_HESAPISL_HSTODEMEID] ON [dbo].[HESAPISL]
	(
		[HSTODEMEID] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
	GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[HESAPISL]') AND name = N'[IX_HESAPISL_IPTAL_ONAYLI_ITARIH_HDOSYAID_ITEMID]')
	CREATE NONCLUSTERED INDEX [IX_HESAPISL_IPTAL_ONAYLI_ITARIH_HDOSYAID_ITEMID] ON [dbo].[HESAPISL]
	(
		[IPTAL] ASC,
		[ONAYLI] ASC,
		[ITARIH] ASC,
		[HDOSYAID] ASC,
		[ITEMID] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
	GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[HESAPISL]') AND name = N'[IX_HESAPISL_IPTAL_ONAYLI_TEKLIF_HSTODEMESIZYAP_ITARIH_ISTDRID]')
	CREATE NONCLUSTERED INDEX [IX_HESAPISL_IPTAL_ONAYLI_TEKLIF_HSTODEMESIZYAP_ITARIH_ISTDRID] ON [dbo].[HESAPISL]
	(
		[ITEMID] ASC,
		[IPTAL] ASC,
		[ONAYLI] ASC,
		[TEKLIF] ASC,
		[HSTODEMESIZYAP] ASC,
		[ITARIH] ASC,
		[ISTDRID] ASC
	)
	INCLUDE ( 	[HESAPISLID],
		[ISTBOLUMID],
		[UYGBOLUMID],
		[UYGDRID],
		[HDOSYAID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
	GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[HESAPISL]') AND name = N'[IX_HESAPISL_ITARIH]')
	CREATE NONCLUSTERED INDEX [IX_HESAPISL_ITARIH] ON [dbo].[HESAPISL]
	(
		[ITARIH] ASC
	)
	INCLUDE ( 	[HESAPISLID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
	GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[HESAPISL]') AND name = N'[IX_HESAPISL_KRMHFATISLID_KRMTUTAR_KRMINDORAN_KRMINDTUTAR]')
	CREATE NONCLUSTERED INDEX [IX_HESAPISL_KRMHFATISLID_KRMTUTAR_KRMINDORAN_KRMINDTUTAR] ON [dbo].[HESAPISL]
	(
		[KRMHFATISLID] ASC,
		[IPTAL] ASC,
		[ONAYLI] ASC,
		[KRMTUTAR] ASC,
		[KRMINDORAN] ASC,
		[KRMINDTUTAR] ASC
	)
	INCLUDE ( 	[HESAPISLID],
		[HDOSYAID],
		[ITARIH],
		[ADET],
		[FIYAT],
		[KRMHKEDTUTAR]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
	GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[HESAPISL]') AND name = N'[IX_HESAPISL_MDLISLEMSIRANO]')
	CREATE NONCLUSTERED INDEX [IX_HESAPISL_MDLISLEMSIRANO] ON [dbo].[HESAPISL]
	(
		[MDLISLEMSIRANO] ASC
	)
	INCLUDE ( 	[HESAPISLID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
	GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[HESAPISL]') AND name = N'IX_HESAPISL_ONAYLI_IPTAL_ITARIH')
	CREATE NONCLUSTERED INDEX [IX_HESAPISL_ONAYLI_IPTAL_ITARIH] ON [dbo].[HESAPISL]
	(
		[ONAYLI] ASC,
		[IPTAL] ASC,
		[ITARIH] ASC
	)
	INCLUDE ( 	[HESAPISLID],
		[TEKLIF],
		[TABLOTIPI],
		[UYGBOLUMID],
		[ITEMID],
		[HDOSYAID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
	GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[HESAPISL]') AND name = N'[IX_HESAPISL_SGODEMEID]')
	CREATE NONCLUSTERED INDEX [IX_HESAPISL_SGODEMEID] ON [dbo].[HESAPISL]
	(
		[SGODEMEID] ASC
	)
	INCLUDE ( 	[HESAPISLID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
	GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[HESAPISL]') AND name = N'[IX_HESAPISL_TABLOTIPI_UYGBOLUMID]')
	CREATE NONCLUSTERED INDEX [IX_HESAPISL_TABLOTIPI_UYGBOLUMID] ON [dbo].[HESAPISL]
	(
		[TABLOTIPI] ASC,
		[UYGBOLUMID] ASC
	)
	INCLUDE ( 	[HESAPISLID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
	GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[HESAPISL]') AND name = N'IX_HESAPISL_UYGDRID_ITARIH')
	CREATE NONCLUSTERED INDEX [IX_HESAPISL_UYGDRID_ITARIH] ON [dbo].[HESAPISL]
	(
		[UYGDRID] ASC,
		[ITARIH] ASC
	)
	INCLUDE ( 	[HESAPISLID],
		[HDOSYAID],
		[IPTAL],
		[TEKLIF],
		[ONAYLI],
		[TABLOTIPI],
		[MDLKAYDET]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
	GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[HESAPISL]') AND name = N'IX_HESAPISL_OZELHFATISLID_IPTAL')
    CREATE NONCLUSTERED INDEX IX_HESAPISL_OZELHFATISLID_IPTAL ON [dbo].[HESAPISL] 
    ([OZELHFATISLID],[IPTAL])
    INCLUDE ([OZELTUTAR],[OZELINDORAN],[OZELINDTUTAR],OZELODENDI,HDOSYAID,DOVIZKODU)
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

DECLARE @BUYUKINDEXGEC BIT=0
--1 personal 2-standart 3-enterprice 4-express 5-sqldatabase
IF (	(select serverproperty('EngineEdition')) IN (3,2)--1 personal 2-standart 3-enterprice 4-express 5-sqldatabase
	AND (SELECT COUNT(TABLOADI) FROM INDEXSIFIRLAMALOG WHERE TABLOADI='HESAPISL' AND ISNULL(BUYUKINDEXGEC,1)=0)=0
	
	AND	(		(select total_physical_memory_kb from sys.dm_os_sys_memory)>50000000 
			OR	(select available_physical_memory_kb from sys.dm_os_sys_memory)>10000000))
	OR (SELECT COUNT(TABLOADI) FROM INDEXSIFIRLAMALOG WHERE TABLOADI='HESAPISL' AND ISNULL(BUYUKINDEXGEC,0)=1)>0
			SET @BUYUKINDEXGEC=1	

IF @BUYUKINDEXGEC=1
BEGIN
	IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[HESAPISL]') AND name = N'IX_HESAPISL_HDOSYAID_IPTAL_TEKLIF_ONAYLI')
		CREATE NONCLUSTERED INDEX [IX_HESAPISL_HDOSYAID_IPTAL_TEKLIF_ONAYLI] ON [dbo].[HESAPISL]
		(
			[HDOSYAID] ASC,
			[IPTAL] ASC,
			[TEKLIF] ASC,
			[ONAYLI] ASC
		)
		INCLUDE ( 	[HESAPISLID],
			[ITEMID],
			[ITARIH],
			[MDLKAYDET],
			[UYGBIRIMID],
			[UYGDRID],
			[UYGBOLUMID],
			[ISTDRID],
			[ISTBOLUMID],
			[MDLKAYDETDEGISTI],
			[TABLOTIPI],
			[ADET],
			[KYKULLANICI],
			[SNKAYDET],
			[SNOGONDERILDI],
			[OPHESAPISLID],
			[ENABIZISLEMBILDIRIM],
			[SNKAYITID],
			[KRMINDORAN]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
END
ELSE
BEGIN
	IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[HESAPISL]') AND name = N'IX_HESAPISL_HDOSYAID_IPTAL_TEKLIF_ONAYLI')
		CREATE NONCLUSTERED INDEX [IX_HESAPISL_HDOSYAID_IPTAL_TEKLIF_ONAYLI] ON [dbo].[HESAPISL]
		(
			[HDOSYAID] ASC,
			[IPTAL] ASC,
			[TEKLIF] ASC,
			[ONAYLI] ASC
		)
		INCLUDE ([HESAPISLID],[ITEMID],[ITARIH],[MDLKAYDET]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
END 

IF @BUYUKINDEXGEC=1
BEGIN
	IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[HESAPISL]') AND name = N'IX_HESAPISL_HESAPISLGENEL')
		CREATE NONCLUSTERED INDEX [IX_HESAPISL_HESAPISLGENEL] ON [dbo].[HESAPISL]
		(
			[HESAPISLID] ASC
		)
		INCLUDE ( 	[IPTAL],
			[ONAYLI],
			[TEKLIF],
			[UYGBOLUMID],
			[OZELODENDI],
			[HSTODEMESIZYAP],
			[ITEMID],
			[ISTBOLUMID],
			[ISTDRID],
			[UYGDRID],
			[SNKAYITID],
			[UYGKURUMID],
			[UYGBIRIMID],
			[HDOSYAID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
END


IF @BUYUKINDEXGEC=1
BEGIN
	IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[HESAPISL]') AND name = N'IX_HESAPISL_TEKLIF_ONAYLI_IPTAL_ITARIH')
		CREATE NONCLUSTERED INDEX [IX_HESAPISL_TEKLIF_ONAYLI_IPTAL_ITARIH] ON [dbo].[HESAPISL]
		(
			[TEKLIF] ASC,
			[ONAYLI] ASC,
			[IPTAL] ASC,
			[ITARIH] ASC
		)
		INCLUDE ( 	[HESAPISLID],
			[HDOSYAID],
			[TABLOTIPI],
			[ITEMID],
			[UYGDRID],
			[DOVIZKODU],
			[SNKAYDET]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
END
ELSE
BEGIN
	IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[HESAPISL]') AND name = N'IX_HESAPISL_TEKLIF_ONAYLI_IPTAL_ITARIH')
		CREATE NONCLUSTERED INDEX [IX_HESAPISL_TEKLIF_ONAYLI_IPTAL_ITARIH] ON [dbo].[HESAPISL]
		(
			[TEKLIF] ASC,
			[ONAYLI] ASC,
			[IPTAL] ASC,
			[ITARIH] ASC
		) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
END
GO

