CREATE PROCEDURE CreateAllIndexesForDB @dbname VARCHAR(100), @tableName VARCHAR(200) AS BEGIN DECLARE @SQL VARCHAR(MAX) SET @SQL ='USE '+@dbname+ ' SELECT ''CREATE '' + CASE WHEN I.is_unique = 1 THEN ''UNIQUE '' ELSE '''' END + I.type_desc COLLATE DATABASE_DEFAULT +'' INDEX '' + I.name + '' ON '' + Schema_name(T.Schema_id)+''.''+ T.name + '' (''+ LTRIM(RTRIM(KeyColumns)) + '')'' + ISNULL(''INCLUDE (''+IncludedColumns+'')'','''') + ISNULL(''WHERE ''+I.Filter_definition,'''') + '' WITH ('' + CASE WHEN I.is_padded = 1 THEN '' PAD_INDEX = ON'' ELSE '' PAD_INDEX = OFF'' END + '','' + -- default value '' SORT_IN_TEMPDB = OFF'' + '','' + --CASE WHEN I.ignore_dup_key = 1 THEN ''IGNORE_DUP_KEY = ON'' ELSE ''IGNORE_DUP_KEY = OFF'' END + '','' + CASE WHEN ST.no_recompute = 0 THEN '' STATISTICS_NORECOMPUTE = OFF'' ELSE '' STATISTICS_NORECOMPUTE = ON'' END + '','' + -- default value '' DROP_EXISTING = OFF'' + '',''+ -- default value '' ONLINE = OFF'' + '','' + CASE WHEN I.allow_row_locks = 1 THEN '' ALLOW_ROW_LOCKS = ON'' ELSE '' ALLOW_ROW_LOCKS = OFF'' END + '','' + CASE WHEN I.allow_page_locks = 1 THEN '' ALLOW_PAGE_LOCKS = ON'' ELSE '' ALLOW_PAGE_LOCKS = OFF'' END+ '',''+ '' FILLFACTOR = ''+ CONVERT(CHAR(5),CASE WHEN I.Fill_factor = 0 THEN 100 ELSE I.Fill_factor END) + '') ON ['' +DS.name + '']'' FROM '+@dbname+'.sys.indexes I JOIN '+@dbname+'.sys.tables T ON T.Object_id = I.Object_id JOIN '+@dbname+'.sys.sysindexes SI ON I.Object_id = SI.id AND I.index_id = SI.indid JOIN (SELECT * FROM ( SELECT IC2.object_id , IC2.index_id , STUFF((SELECT '' , '' + C.name + CASE WHEN MAX(CONVERT(INT,IC1.is_descending_key)) = 1 THEN '' DESC '' ELSE '' ASC '' END FROM '+@dbname+'.sys.index_columns IC1 JOIN '+@dbname+'.Sys.columns C ON C.object_id = IC1.object_id AND C.column_id = IC1.column_id AND IC1.is_included_column = 0 WHERE IC1.object_id = IC2.object_id AND IC1.index_id = IC2.index_id GROUP BY IC1.object_id,C.name,index_id ORDER BY MAX(IC1.key_ordinal) FOR XML PATH('''')), 1, 2, '''') KeyColumns FROM '+@dbname+'.sys.index_columns IC2 --WHERE IC2.Object_id = object_id(''Person.Address'') --Comment for all tables GROUP BY IC2.object_id ,IC2.index_id) tmp3 )tmp4 ON I.object_id = tmp4.object_id AND I.index_id = tmp4.index_id JOIN '+@dbname+'.sys.stats ST ON ST.object_id = I.object_id AND ST.stats_id = I.index_id JOIN '+@dbname+'.sys.data_spaces DS ON I.data_space_id=DS.data_space_id JOIN '+@dbname+'.sys.filegroups FG ON I.data_space_id=FG.data_space_id LEFT JOIN (SELECT * FROM ( SELECT IC2.object_id , IC2.index_id , STUFF((SELECT '' , '' + C.name FROM '+@dbname+'.sys.index_columns IC1 JOIN '+@dbname+'.Sys.columns C ON C.object_id = IC1.object_id AND C.column_id = IC1.column_id AND IC1.is_included_column = 1 WHERE IC1.object_id = IC2.object_id AND IC1.index_id = IC2.index_id GROUP BY IC1.object_id,C.name,index_id FOR XML PATH('''')), 1, 2, '''') IncludedColumns FROM '+@dbname+'.sys.index_columns IC2 --WHERE IC2.Object_id = object_id(''Person.Address'') --Comment for all tables GROUP BY IC2.object_id ,IC2.index_id) tmp1 WHERE IncludedColumns IS NOT NULL ) tmp2 ON tmp2.object_id = I.object_id AND tmp2.index_id = I.index_id WHERE T.name = '''+@tableName+''' ' PRINT (@sql) --WHERE I.is_primary_key = 0 AND I.is_unique_constraint = 0 --AND I.name like ''%DBA%'' --comment for all indexes --AND I.Object_id = object_id('Person.Address') --Comment for all tables --AND I.name = 'IX_Address_PostalCode'' --comment for all indexes END
