Sometimes, we need to script out the index definitions and its option settings. If you have a source control then you are lucky, as all index definitions should be kept in your source control system. If you want to just script out indexes for a table, you were also able to do so by selecting all indexes in the object explorer details. But if we have a query can easily script out the index definition and its settings then it will be much easier for testing or index creation automation.
In SQL server, the index definition and option settings are not stored in a single table, you have to join a few system tables or DMVs together to get all those information. Here I will share the script I used to script out all the indexes.
My consideration:
1. Only apply to the user defined indexes;
2. Clustered indexes created with Primary Key constraint is not included though it can be done.
3. Spatial Indexes and XML Indexes are not considered.
4. The Data compression setting for Partitioned indexes is simplified here as it is pretty complicated to be done just in a select statement.
5. The Drop_Existing and Sort_In_TempDB setting is hard-coded in the script, which depending on your situation you can change it.
The script is as below:
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 = OFF, 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 is_primary_key = 0 --this is not for primary key constraint
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
OPTION (Recompile);
I have also attached a sql script file here:
Scriptout_Indexes.sql (4.95 kb)