SELECT SCHEMA_NAME (O.schema_id) SchemaName ,o.name ObjectName,i.name IndexName ,i.type_desc ,LEFT(list, ISNULL(splitter-1,LEN(list))) Columns , SUBSTRING(list, indCol.splitter +1, 100) includedColumns--len(name) - splitter-1) columns , COUNT(1) OVER (PARTITION BY o.object_id) FROM sys.indexes i join sys.objects o ON i.object_id = o.object_id cross apply ( SELECT NULLIF(charindex('|',indexCols.list),0) splitter , list FROM (SELECT cast(( SELECT CASE WHEN sc.is_included_column = 1 and sc.ColPos = 1 THEN '|' ELSE '' END + CASE WHEN sc.ColPos > 1 THEN ', ' ELSE '' END + name FROM ( SELECT sc.is_included_column, index_column_id, name, ROW_NUMBER() OVER (PARTITION BY sc.is_included_column ORDER by sc.index_column_id) ColPos FROM sys.index_columns sc JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id WHERE sc.index_id = i.index_id AND sc.object_id = i.object_id ) sc ORDER BY sc.is_included_column,ColPos FOR XML PATH (''), TYPE) as varchar(max)) list)indexCols ) indCol ORDER BY SchemaName, ObjectName, IndexName
