[SQL] Index definition query

I want to get the Index definition with all included columns in the index using SQL query.

Below query doesn't seems to be working, any help would be appreciated.

WITH IndexInfo AS (
SELECT
t.name AS TableName,
i.name AS IndexName,
i.index_id,
STUFF((
SELECT ',' + c.name
FROM sys.index_columns ic
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE ic.object_id = i.object_id
AND ic.index_id = i.index_id
AND ic.is_included_column = 0 -- Key columns only
ORDER BY ic.key_ordinal
FOR XML PATH('')), 1, 1, '') AS KeyColumns,
STUFF((
SELECT ',' + c.name
FROM sys.index_columns ic
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE ic.object_id = i.object_id
AND ic.index_id = i.index_id
AND ic.is_included_column = 1 -- Included columns only
FOR XML PATH('')), 1, 1, '') AS IncludedColumns
FROM
sys.indexes i
JOIN
sys.tables t ON i.object_id = t.object_id
WHERE
i.is_primary_key = 0 -- Exclude primary key indexes
AND i.is_unique = 0 -- Exclude unique indexes
)

SELECT
TableName,
IndexName,
KeyColumns,
IncludedColumns
FROM
IndexInfo
WHERE
EXISTS (
SELECT 1
FROM IndexInfo ii
WHERE ii.TableName = IndexInfo.TableName
AND ii.KeyColumns = IndexInfo.KeyColumns
AND ii.IncludedColumns = IndexInfo.IncludedColumns
AND ii.IndexName <> IndexInfo.IndexName -- Find duplicate but different indexes
)
ORDER BY
TableName, KeyColumns, IncludedColumns;


I want to get the Index definition with all included columns in the index using SQL query.

Continue reading...