Scripting out all indexes

The following script will script out all indexes and can be modified to do just clustered or non-clustered by adjusting the upper CTE query WHERE clause (highlighted with comments). You can add the DROP_EXISTING=ON if you want to move an index to a new filegroup ( for example ). Add this to the bottom query.

WITH    indexCTE
AS ( SELECT DISTINCT
o.object_id
, i.index_id
, i.name AS IndexName
, i.is_unique
, i.has_filter
, i.filter_definition
, ds.name AS FilegroupName
, p.data_compression
, i.is_padded
, i.fill_factor
, i.ignore_dup_key
, i.allow_row_locks
, i.allow_page_locks
FROM     sys.objects AS o
INNER JOIN sys.indexes AS i ON i.object_id = o.object_id
INNER JOIN sys.data_spaces AS ds ON ds.data_space_id = i.data_space_id
INNER JOIN sys.partitions AS p ON p.object_id = i.object_id AND p.index_id = i.index_id –inner join causes exclusion of XML Indexes
INNER JOIN sys.index_columns AS ic ON ic.object_id = i.object_id
AND ic.index_id = i.index_id
INNER JOIN sys.columns AS c ON c.column_id = ic.column_id
WHERE    o.is_ms_shipped = 0
AND i.index_id > 0
AND i.is_primary_key = 0 –comment out to include PK’s
AND i.is_unique_constraint = 0 –comment out to include unique constraints
) ,
indexCTE2
AS ( SELECT   OBJECT_SCHEMA_NAME(indexCTE.[object_id]) AS SchemaName
, OBJECT_NAME(indexCTE.[object_id]) AS TableName
, IndexName
, CASE indexCTE.is_unique WHEN 1 THEN ‘ UNIQUE’ ELSE ” END AS UniqueIndex
, indexCTE.has_filter
, indexCTE.filter_definition
, indexCTE.FilegroupName
, CASE indexCTE.index_id WHEN 1 THEN ‘ CLUSTERED’ ELSE ‘ NONCLUSTERED’ END AS IndexType
, indexCTE.data_compression
, CAST(indexCTE.is_padded AS VARCHAR(3)) AS is_padded
, CAST(indexCTE.fill_factor AS VARCHAR(3)) AS fill_factor
, CAST(indexCTE.ignore_dup_key AS VARCHAR(3)) AS ignore_dup_key
, CAST(indexCTE.allow_row_locks AS VARCHAR(3)) AS allow_row_locks
, CAST(indexCTE.allow_page_locks AS VARCHAR(3)) AS allow_page_locks
, ( SELECT    CASE WHEN ic.is_descending_key = 1
THEN ‘[‘ + c.name + ‘] DESC, ‘
ELSE ‘[‘ + c.name + ‘] ASC, ‘
END
FROM      sys.columns AS c
INNER JOIN sys.index_columns AS ic ON c.object_id = ic.object_id
AND ic.column_id = c.column_id
AND ic.Is_Included_Column = 0
WHERE     indexCTE.object_id = ic.object_id
AND indexCTE.index_id = ic.index_id
ORDER BY ic.key_ordinal
FOR
XML PATH(”)
) AS ixcols
, ISNULL(( SELECT DISTINCT ‘[‘ + c.name + ‘], ‘
FROM   sys.columns AS c
INNER JOIN sys.index_columns AS ic ON c.object_id = ic.object_id
AND ic.column_id = c.column_id
AND ic.Is_Included_Column = 1
WHERE  indexCTE.OBJECT_ID = ic.object_id
AND indexCTE.index_id = ic.index_id
–ORDER BY ic.key_ordinal
FOR
XML PATH(”)
), ”) AS includedcols
FROM     indexCTE
)
SELECT  ‘CREATE’ + UniqueIndex + IndexType + ‘ INDEX [‘ + IndexName + ‘] ON [‘ + SchemaName + ‘].[‘ + TableName + ‘]’
+ ‘ ( ‘ + SUBSTRING(ixcols, 1, LEN(ixcols) – 1) + CASE LEN(includedcols) WHEN 0 THEN ‘ )’ ELSE ‘ ) INCLUDE ( ‘ + SUBSTRING(includedcols, 1, LEN(includedcols) – 1) + ‘ ) ‘ END
+ CASE WHEN has_filter = 1 THEN ‘ WHERE ‘ + filter_definition ELSE ” END
+ ‘ WITH ( PAD_INDEX = ‘ + CASE WHEN is_padded = 1 THEN ‘ON’ ELSE ‘OFF’ END + ‘, ‘
+ CASE WHEN CAST(SERVERPROPERTY(‘Edition’) AS VARCHAR) LIKE ‘Enterprise%’ THEN ‘ ONLINE = ON ‘ ELSE ‘ ONLINE = OFF’ END + ‘, ‘
+ CASE WHEN fill_factor > 0 THEN ‘FILLFACTOR = ‘ + fill_factor + ‘, ‘ ELSE ” END
+ ‘IGNORE_DUP_KEY =’ + CASE WHEN [ignore_dup_key] = 1 THEN ‘ ON’ ELSE ‘ OFF’ END + ‘, ‘
+ ‘ALLOW_ROW_LOCKS =’ + CASE WHEN [allow_row_locks] = 1 THEN ‘ ON’ ELSE ‘ OFF’ END + ‘, ‘
+ ‘ALLOW_PAGE_LOCKS =’ + CASE WHEN [allow_row_locks] = 1 THEN ‘ ON’ ELSE ‘ OFF’ END
+ CASE indexCTE2.data_compression WHEN 0 THEN ‘ )’ WHEN 1 THEN ‘, DATA_COMPRESSION = ROW )’ WHEN 2 THEN ‘, DATA_COMPRESSION = PAGE )’ ELSE ” END
+ ‘ ON [‘ + FilegroupName + ‘];’
FROM    indexCTE2

This entry was posted in T-SQL. Bookmark the permalink.