IF OBJECT_ID('tempdb..#Indexes') IS NOT NULL
DROP TABLE #Indexes
IF OBJECT_ID('tempdb..#Indexes') IS NULL
WITH objects
AS (SELECT sc.name AS SchemaName,
tb.name AS ObjectName,
tb.OBJECT_ID
FROM sys.objects tb (NOLOCK)
INNER JOIN sys.schemas sc (NOLOCK) ON tb.SCHEMA_ID = sc.SCHEMA_ID),
indexColumns
AS (SELECT TOP 99999999
tb.OBJECT_ID,
SchemaName,
ObjectName,
CASE WHEN ix.is_primary_key = 1
OR ix.is_unique_constraint = 1 THEN ix.name
ELSE ix.name
END AS IndexName,
ix.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS IndexType,
ds.type AS DataspaceType,
ds.name AS DataspaceName,
ix.index_id,
ix.is_unique,
ix.is_primary_key,
ix.is_unique_constraint,
ix.filter_definition,
pr.data_compression_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS DATA_COMPRESSION,
CASE WHEN ic.key_ordinal > 0 THEN '[' + cl.name + '] ' + CASE WHEN ic.is_descending_key = 1 THEN 'DESC'
ELSE 'ASC'
END
ELSE NULL
END AS IndexCol,
CASE WHEN ic.is_included_column > 0 THEN '[' + cl.name + ']'
ELSE NULL
END AS IncludeCol,
CASE WHEN ic.partition_ordinal > 0 THEN '[' + cl.name + ']'
ELSE NULL
END AS PartitionedCol
FROM objects tb
INNER JOIN sys.indexes AS ix WITH (NOLOCK) ON tb.OBJECT_ID = ix.OBJECT_ID
INNER JOIN sys.data_spaces AS ds WITH (NOLOCK) ON ds.data_space_id = ix.data_space_id
INNER JOIN sys.partitions AS pr WITH (NOLOCK) ON pr.OBJECT_ID = ix.OBJECT_ID
AND pr.index_id = ix.index_id
INNER JOIN sys.index_columns AS ic WITH (NOLOCK) ON ic.OBJECT_ID = ix.OBJECT_ID
AND ic.index_id = ix.index_id
INNER JOIN sys.columns AS cl WITH (NOLOCK) ON cl.OBJECT_ID = ic.OBJECT_ID
AND cl.column_id = ic.column_id
WHERE ix.index_id > 0
AND pr.partition_number = 1
ORDER BY tb.OBJECT_ID,
ix.type,
ix.is_primary_key,
ix.is_unique_constraint,
ix.index_id,
ic.key_ordinal)
SELECT SchemaName,
ObjectName,
IndexType,
index_id,
is_unique,
is_primary_key,
is_unique_constraint,
IndexName,
data_compression,
DataspaceType,
DataspaceName,
Util.dbo.StringConcat(DISTINCT PartitionedCol, '') AS PartitionedCol,
Util.dbo.StringConcat(IndexCol, ', ') AS IndexColumns,
Util.dbo.StringConcat(IncludeCol, ', ') AS IncludeColumns,
filter_definition
INTO #Indexes
FROM IndexColumns ic
GROUP BY SchemaName,
ObjectName,
IndexType,
index_id,
is_unique,
is_primary_key,
is_unique_constraint,
IndexName,
filter_definition,
data_compression,
DataspaceType,
DataspaceName
ORDER BY SchemaName,
ObjectName,
index_id,
IndexType,
is_unique,
is_primary_key,
is_unique_constraint,
IndexName
SELECT *
FROM #Indexes