How to create Drop and Recreate Script of all Indexes in a database?
I found this interesting script once so thought of sharing with everyone. Special thanks to the Unknown-Creator of this script.
This script generates the ‘create’ & ‘drop’ index script for each and every index in a database. I have tried this number of times and it does what is expected from it. What all you need to do is, run this script on your database and look for the columns ‘Index_Create_Statement’ or ‘Index_Drop_Statement’
————-Start Copy —————————-
Select A.[object_id] , OBJECT_NAME(A.[object_id]) AS Table_Name , A.Index_ID , A.[Name] As Index_Name , CAST( Case When A.type = 1 AND is_unique = 1 Then 'Create Unique Clustered Index ' When A.type = 1 AND is_unique = 0 Then 'Create Clustered Index ' When A.type = 2 AND is_unique = 1 Then 'Create Unique NonClustered Index ' When A.type = 2 AND is_unique = 0 Then 'Create NonClustered Index ' End + quotename(A.[Name]) + ' On ' + quotename(S.name) + '.' + quotename(OBJECT_NAME(A.[object_id])) + ' (' + Stuff( ( Select ',[' + COL_NAME(A.[object_id],C.column_id) + Case When C.is_descending_key = 1 Then '] Desc' Else '] Asc' End From sys.index_columns C WITH (NOLOCK) Where A.[Object_ID] = C.object_id And A.Index_ID = C.Index_ID And C.is_included_column = 0 Order by C.key_Ordinal Asc For XML Path('') ) ,1,1,'') + ') ' + CASE WHEN A.type = 1 THEN '' ELSE Coalesce('Include (' + Stuff( ( Select ',' + QuoteName(COL_NAME(A.[object_id],C.column_id)) From sys.index_columns C WITH (NOLOCK) Where A.[Object_ID] = C.object_id And A.Index_ID = C.Index_ID And C.is_included_column = 1 Order by C.index_column_id Asc For XML Path('') ) ,1,1,'') + ') ' ,'') End + Case When A.has_filter = 1 Then 'Where ' + A.filter_definition Else '' End + ' With (Drop_Existing = ON, SORT_IN_TEMPDB = ON' --when the same index exists you'd better to set the Drop_Existing = ON --SORT_IN_TEMPDB = ON is recommended but based on your own environment. + ', Fillfactor = ' + Cast(Case When fill_factor = 0 Then 100 Else fill_factor End As varchar(3)) + Case When A.[is_padded] = 1 Then ', PAD_INDEX = ON' Else ', PAD_INDEX = OFF' END + Case When D.[no_recompute] = 1 Then ', STATISTICS_NORECOMPUTE = ON' Else ', STATISTICS_NORECOMPUTE = OFF' End + Case When A.[ignore_dup_key] = 1 Then ', IGNORE_DUP_KEY = ON' Else ', IGNORE_DUP_KEY = OFF' End + Case When A.[ALLOW_ROW_LOCKS] = 1 Then ', ALLOW_ROW_LOCKS = ON' Else ', ALLOW_ROW_LOCKS = OFF' END + Case When A.[ALLOW_PAGE_LOCKS] = 1 Then ', ALLOW_PAGE_LOCKS = ON' Else ', ALLOW_PAGE_LOCKS = OFF' End + Case When P.[data_compression] = 0 Then ', DATA_COMPRESSION = NONE' When P.[data_compression] = 1 Then ', DATA_COMPRESSION = ROW' Else ', DATA_COMPRESSION = PAGE' End + ') On ' + Case when C.type = 'FG' THEN quotename(C.name) ELSE quotename(C.name) + '(' + F.Partition_Column + ')' END + ';' --if it uses partition scheme then need partition column As nvarchar(Max)) As Index_Create_Statement , C.name AS FileGroupName , 'DROP INDEX ' + quotename(A.[Name]) + ' On ' + quotename(S.name) + '.' + quotename(OBJECT_NAME(A.[object_id])) + ';' AS Index_Drop_Statement From SYS.Indexes A WITH (NOLOCK) INNER JOIN sys.objects B WITH (NOLOCK) ON A.object_id = B.object_id INNER JOIN SYS.schemas S ON B.schema_id = S.schema_id INNER JOIN SYS.data_spaces C WITH (NOLOCK) ON A.data_space_id = C.data_space_id INNER JOIN SYS.stats D WITH (NOLOCK) ON A.object_id = D.object_id AND A.index_id = D.stats_id Inner Join --The below code is to find out what data compression type was used by the index. If an index is not partitioned, it is easy as only one data compression --type can be used. If the index is partitioned, then each partition can be configued to use the different data compression. This is hard to generalize, --for simplicity, I just use the data compression type used most for the index partitions for all partitions. You can later rebuild the index partition to --the appropriate data compression type you want to use ( select object_id, index_id, Data_Compression, ROW_NUMBER() Over(Partition By object_id, index_id Order by COUNT(*) Desc) As Main_Compression From sys.partitions WITH (NOLOCK) Group BY object_id, index_id, Data_Compression ) P ON A.object_id = P.object_id AND A.index_id = P.index_id AND P.Main_Compression = 1 Outer APPLY ( SELECT COL_NAME(A.object_id, E.column_id) AS Partition_Column From sys.index_columns E WITH (NOLOCK) WHERE E.object_id = A.object_id AND E.index_id = A.index_id AND E.partition_ordinal = 1 ) F Where A.type IN (1,2) --clustered and nonclustered AND B.Type != 'S' AND OBJECT_NAME(A.[object_id]) not like 'queue_messages_%' AND OBJECT_NAME(A.[object_id]) not like 'filestream_tombstone_%' AND OBJECT_NAME(A.[object_id]) not like 'sys%' --if you have index start with sys then remove it