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

DECLARE @TABLE NVARCHAR(50)='HDOSYA'
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].[HDOSYA]') AND name = N'IX_HDOSYA_BOLUMID_HDIPTAL_SUBEID_HDTEKLIF_HDTARIH')
	CREATE NONCLUSTERED INDEX [IX_HDOSYA_BOLUMID_HDIPTAL_SUBEID_HDTEKLIF_HDTARIH] ON [dbo].[HDOSYA]
	(
		[BOLUMID] ASC,
		[HDIPTAL] ASC,
		[SUBEID] ASC,
		[HDTEKLIF] ASC,
		[HDTARIH] ASC
	)
	INCLUDE ( 	[HDOSYAID],
		[HKIMLIKID]) 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].[HDOSYA]') AND name = N'IX_HDOSYA_DRID')
	CREATE NONCLUSTERED INDEX [IX_HDOSYA_DRID] ON [dbo].[HDOSYA]
	(
		[DRID] ASC
	)
	INCLUDE ( 	[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].[HDOSYA]') AND name = N'IX_HDOSYA_ICMALID')
	CREATE NONCLUSTERED INDEX [IX_HDOSYA_ICMALID] ON [dbo].[HDOSYA]
	(	
		[ICMALID] 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].[HDOSYA]') AND name = N'IX_HDOSYA_HDSGKURUMID_SUBEID_SGICMALID_HDIPTAL_HDTARIH')
	CREATE NONCLUSTERED INDEX [IX_HDOSYA_HDSGKURUMID_SUBEID_SGICMALID_HDIPTAL_HDTARIH] ON [dbo].[HDOSYA]
	(
		[HDSGKURUMID] ASC,
		[SUBEID] ASC,
		[SGICMALID] ASC,
		[HDIPTAL] ASC,
		[HDTARIH] 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].[HDOSYA]') AND name = N'IX_HDOSYA_HDTARIH')
	CREATE NONCLUSTERED INDEX [IX_HDOSYA_HDTARIH] ON [dbo].[HDOSYA]
	(
		[HDTARIH] ASC
	)
	INCLUDE ( 	[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].[HDOSYA]') AND name = N'IX_HDOSYA_HKCARIID_ICMALID_HDTARIH')
	CREATE NONCLUSTERED INDEX [IX_HDOSYA_HKCARIID_ICMALID_HDTARIH] ON [dbo].[HDOSYA]
	(
		[HKCARIID] ASC,
		[ICMALID] ASC,
		[HDTARIH] ASC
	)
	INCLUDE ( 	[HDOSYAID],
		[SUBEID],
		[HDIPTAL],
		[MDLTEDAVITURU],
		[BOLUMID],
		[GSSTABURCUKODU],
		[GSSTAKIPTIP],
		[GSSPROVTIPI],
		[GSSODEMEDURUM],
		[GSSDEVKURUM],
		[HKIMLIKID],
		[DRID],
		[MDLAYRIFATNEDENID]) 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].[HDOSYA]') AND name = N'IX_HDOSYA_HKIMLIKID')
	CREATE NONCLUSTERED INDEX [IX_HDOSYA_HKIMLIKID] ON [dbo].[HDOSYA]
	(
		[HKIMLIKID] ASC
	)
	INCLUDE ( 	[HDOSYAID],
		[SUBEID],
		[HKCARIID],
		[DRID],
		[HDTARIH],
		[BOLUMID],
		[HDYATIYOR],
		[GSSPROVTIPI]) 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].[HDOSYA]') AND name = N'IX_HDOSYA_MDLBASVURUNO')
	CREATE NONCLUSTERED INDEX [IX_HDOSYA_MDLBASVURUNO] ON [dbo].[HDOSYA]
	(
		[MDLBASVURUNO] 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].[HDOSYA]') AND name = N'IX_HDOSYA_MDLHBSVFATURALAMAID')
	CREATE NONCLUSTERED INDEX [IX_HDOSYA_MDLHBSVFATURALAMAID] ON [dbo].[HDOSYA]
	(
		[MDLHBSVFATURALAMAID] ASC
	)
	INCLUDE ( 	[HDOSYAID],
		[MDLDOSYADURUM],
		[KRMNETTUTAR],
		[HDMDLKRMTUTAR]) 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].[HDOSYA]') AND name = N'IX_HDOSYA_MDLILKTAKIPNO')
	CREATE NONCLUSTERED INDEX [IX_HDOSYA_MDLILKTAKIPNO] ON [dbo].[HDOSYA]
	(
		[MDLILKTAKIPNO] ASC
	)
	INCLUDE ( 	[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].[HDOSYA]') AND name = N'IX_HDOSYA_MDLTAKIPNO')
	CREATE NONCLUSTERED INDEX [IX_HDOSYA_MDLTAKIPNO] ON [dbo].[HDOSYA]
	(
		[MDLTAKIPNO] ASC
	)
	INCLUDE ( 	[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].[HDOSYA]') AND name = N'IX_HDOSYA_MDLTAKIPTARIHI')
	CREATE NONCLUSTERED INDEX [IX_HDOSYA_MDLTAKIPTARIHI] ON [dbo].[HDOSYA]
	(
		[MDLTAKIPTARIHI] ASC
	)
	INCLUDE ( 	[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].[HDOSYA]') AND name = N'IX_HDOSYA_MDLTEDAVITURU_ MDLBASVURUNO_HDOSYAID')
	CREATE NONCLUSTERED INDEX [IX_HDOSYA_MDLTEDAVITURU_ MDLBASVURUNO_ HDOSYAID] ON [dbo].[HDOSYA]
	(
		[MDLTEDAVITURU] ASC,
		[MDLBASVURUNO] ASC
	)
	INCLUDE ( 	[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].[HDOSYA]') AND name = N'IX_HDOSYA_SUBEID_HDIPTAL_HDTEKLIF')
	CREATE NONCLUSTERED INDEX [IX_HDOSYA_SUBEID_HDIPTAL_HDTEKLIF] ON [dbo].[HDOSYA]
	(
		[SUBEID] ASC,
		[HDIPTAL] ASC,
		[HDTEKLIF] 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].[HDOSYA]') AND name = N'IX_HDOSYA_SUBEID_MDLTAKIPVAR_HDIPTAL_HDTARIH_MDLTAKIPTARIHI')
	CREATE NONCLUSTERED INDEX [IX_HDOSYA_SUBEID_MDLTAKIPVAR_HDIPTAL_HDTARIH_MDLTAKIPTARIHI] ON [dbo].[HDOSYA]
	(
		[SUBEID] ASC,
		[MDLTAKIPVAR] ASC,
		[HDIPTAL] ASC,
		[HDTARIH] ASC,
		[MDLTAKIPTARIHI] 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

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='HDOSYA' 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='HDOSYA' 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].[HDOSYA]') AND name = N'IX_HDOSYA_HDOSYAGENEL')
		CREATE UNIQUE NONCLUSTERED INDEX [IX_HDOSYA_HDOSYAGENEL] ON [dbo].[HDOSYA]
		(
			[HDOSYAID] ASC
		)
		INCLUDE ( 	[HKCARIID],
			[HDYATIYOR],
			[ARACIID],
			[HSTPAKETVAR],
			[KRMPAKETVAR],
			[HKIMLIKID],
			[HDSGKURUMID],
			[SUBEID],
			[SGPAKETVAR],
			[HSTNETTUTAR],
			[KRMNETTUTAR],
			[SGNETTUTAR],
			[HDACIKLAMA],
			[TOPKESINTIYZD],
			[TOPKESINTITUTAR],
			[TOPHSTKESINTIYZD],
			[TOPHSTKESINTITUTAR],
			[TOPKRMKESINTIYZD],
			[TOPKRMKESINTITUTAR],
			[TOPSGKESINTIYZD],
			[TOPSGKESINTITUTAR],
			[HOSTESID],
			[DRID],
			[BOLUMID],
			[GSSSORGUTURU],
			[HDOSYANO],
			[HDSICILNO],
			[MDLTEDAVITURU],
			[MDLDOSYADURUM],
			[HSTODEMETUTAR],
			[HSTKESINTITUTAR],
			[HSTODEINDTUTAR],
			[YESILALANM],
			[KURUMTAMAM],
			[POLPROTOKOL],
			[GSSPROVTIPI],
			[SERVISPROTOKOLNO],
			[SNOTAKIPNO],
			[HDTARIH]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
END
GO

