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

DECLARE @TABLE NVARCHAR(50)='FATISL'
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].[FATISL]') AND name = N'IX_FATISL_FATID')
	CREATE NONCLUSTERED INDEX [IX_FATISL_FATID] ON [dbo].[FATISL]
	(
		[FATID] 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].[FATISL]') AND name = N'IX_FATISL_IRSISLID')
	CREATE NONCLUSTERED INDEX [IX_FATISL_IRSISLID] ON [dbo].[FATISL]
	(
		[IRSISLID] ASC
	)
	INCLUDE ( 	[FATISLID]) 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].[FATISL]') AND name = N'IX_FATISL_SATIRIND')
	CREATE NONCLUSTERED INDEX [IX_FATISL_SATIRIND] ON [dbo].[FATISL]
	(
		[SATIRIND] ASC
	)
	INCLUDE ( 	[FATISLID],
		[FATID],
		[STOKID]) 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].[FATISL]') AND name = N'IX_FATISL_STOKID_SATIRINDID')
	CREATE NONCLUSTERED INDEX [IX_FATISL_STOKID_SATIRINDID] ON [dbo].[FATISL]
    (
	    [STOKID] ASC,
	    [SATIRIND] ASC
    )
    INCLUDE ( 	[FATISLID],
	    [FATID]) 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].[FATISL]') AND name = N'IX_FATISL_SATINALMAISLID')
    CREATE NONCLUSTERED INDEX [IX_FATISL_SATINALMAISLID]
    ON [dbo].[FATISL] ([SATINALMAISLID])
    INCLUDE ([FATID],[STOKID],[ADET],[KDV])
    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='FATISL' 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='FATISL' 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].[FATISL]') AND name = N'IX_FATISL_STOKID_FATID_SATIRIND_BFIYAT_FATISLID')
		CREATE NONCLUSTERED INDEX [IX_FATISL_STOKID_FATID_SATIRIND_BFIYAT_FATISLID] ON [dbo].[FATISL]
		(
			[STOKID] ASC,
			[FATID] ASC,
			[SATIRIND] ASC,
			[BFIYAT] ASC,
			[FATISLID] ASC
		)
		INCLUDE ( 	[ACIKLAMA],
			[ADET],
			[BARKOD],
			[FBIRIM],
			[FADET],
			[HESAPID],
			[FBFIYAT],
			[TEVORAN],
			[SKTARIHI],
			[SATIRIND2],
			[SATIRINDT],
			[SATISTID],
			[KDV],
			[IRSISLID],
			[RGDEPOID]) 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].[FATISL]') AND name = N'IX_FATISL_STOKID_FATID_SATIRIND_BFIYAT_FATISLID')
		CREATE NONCLUSTERED INDEX [IX_FATISL_STOKID_FATID_SATIRIND_BFIYAT_FATISLID] ON [dbo].[FATISL]
		(
			[STOKID] ASC,
			[FATID] ASC,
			[SATIRIND] ASC,
			[BFIYAT] ASC,
			[FATISLID] 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
