SELECT
A.object_id AS ObjectID
,OBJECT_NAME(A.object_id) AS TableName
,A.index_id AS IndexID
,A.name As IndexName
,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' +
', 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 + ';'
AS NVARCHAR(MAX)
) As IndexCreateStatement
,C.name AS FileGroupName
,'DROP INDEX ' + QUOTENAME(A.Name) + ' ON ' + QUOTENAME(S.name) + '.' + QUOTENAME(OBJECT_NAME(A.object_id)) + ';' AS IndexDropStatement
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
(
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)
AND B.[type] != 'S'
AND is_primary_key = 0
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%'
OPTION(Recompile);
