with connect(schema_name,table_name,index_name,index_column_id,column_name) as
(   select s.name schema_name, t.name table_name, i.name index_name, index_column_id, cast(c.name as varchar(max)) column_name
 from sys.tables t
inner join sys.schemas s on t.schema_id = s.schema_id
inner join sys.indexes i on i.object_id = t.object_id
inner join sys.index_columns ic on ic.object_id = t.object_id and ic.index_id=i.index_id
        inner join sys.columns c on c.object_id = t.object_id and
                ic.column_id = c.column_id
                where index_column_id=1
union all
select s.name schema_name, t.name table_name, i.name index_name, ic.index_column_id, cast(connect.column_name + ',' + c.name as varchar(max)) column_name
 from sys.tables t
inner join sys.schemas s on t.schema_id = s.schema_id
inner join sys.indexes i on i.object_id = t.object_id
inner join sys.index_columns ic on ic.object_id = t.object_id and ic.index_id=i.index_id
        inner join sys.columns c on c.object_id = t.object_id and
                ic.column_id = c.column_id join connect on
connect.index_column_id+1 = ic.index_column_id
and connect.schema_name = s.name
and connect.table_name = t.name
and connect.index_name = i.name)
select connect.schema_name,connect.table_name,connect.index_name,connect.column_name
from connect join (select schema_name,table_name,index_name,MAX(index_column_id) index_column_id
from connect group by schema_name,table_name,index_name) mx
on connect.schema_name = mx.schema_name
and connect.table_name = mx.table_name
and connect.index_name = mx.index_name
and connect.index_column_id = mx.index_column_id
order by 1,2,3

我可以大胆回答这个饱和的问题吗?

这是@marc_s答案的自由重做，混合了来自@Tim Ford的一些东西，目标是有一个更干净和更简单的结果集和最终显示和排序，以满足我当前的需要。

SELECT 
    OBJECT_SCHEMA_NAME(t.[object_id],DB_ID()) AS [Schema],
    t.[name] AS [TableName], 
    ind.[name] AS [IndexName], 
    col.[name] AS [ColumnName],
    ic.column_id AS [ColumnId],
    ind.[type_desc] AS [IndexTypeDesc], 
    col.is_identity AS [IsIdentity],
    ind.[is_unique] AS [IsUnique],
    ind.[is_primary_key] AS [IsPrimaryKey],
    ic.[is_descending_key] AS [IsDescendingKey],
    ic.[is_included_column] AS [IsIncludedColumn]
FROM 
    sys.indexes ind 
INNER JOIN 
    sys.index_columns ic 
    ON ind.object_id = ic.object_id AND ind.index_id = ic.index_id 
INNER JOIN 
    sys.columns col 
    ON ic.object_id = col.object_id and ic.column_id = col.column_id 
INNER JOIN 
    sys.tables t 
    ON ind.object_id = t.object_id 
WHERE 
    t.is_ms_shipped = 0
    --ind.is_primary_key = 1 -- include or not pks, etc
    --AND ind.is_unique = 0
    --AND ind.is_unique_constraint = 0 
ORDER BY 
    [Schema],
    TableName, 
    IndexName,
    [ColumnId],
    ColumnName

正确的一个在这里(当我们在一个表上有多个索引时，以上所有帖子都会给出笛卡尔积结果)

select s.name, t.name, i.name, c.name from sys.tables t
inner join sys.schemas s on t.schema_id = s.schema_id
inner join sys.indexes i on i.object_id = t.object_id
inner join sys.index_columns ic on ic.object_id = t.object_id 
                                  AND i.index_id = ic.index_id
inner join sys.columns c on c.object_id = t.object_id 
                                  and  ic.column_id = c.column_id
where i.index_id > 0    
 and i.type in (1, 2) -- clustered & nonclustered only
 and i.is_primary_key = 0 -- do not include PK indexes
 and i.is_unique_constraint = 0 -- do not include UQ
 and i.is_disabled = 0
 and i.is_hypothetical = 0
 and ic.key_ordinal > 0
 AND  t.name = 'DimCustomer'
order by ic.key_ordinal

我想到了这个，它给了我我需要的准确的概述。有帮助的是，每个索引得到一行，索引列被聚合到其中。

select 
    o.name as ObjectName, 
    i.name as IndexName, 
    i.is_primary_key as [PrimaryKey],
    SUBSTRING(i.[type_desc],0,6) as IndexType,
    i.is_unique as [Unique],
    Columns.[Normal] as IndexColumns,
    Columns.[Included] as IncludedColumns
from sys.indexes i 
join sys.objects o on i.object_id = o.object_id
cross apply
(
    select
        substring
        (
            (
                select ', ' + co.[name]
                from sys.index_columns ic
                join sys.columns co on co.object_id = i.object_id and co.column_id = ic.column_id
                where ic.object_id = i.object_id and ic.index_id = i.index_id and ic.is_included_column = 0
                order by ic.key_ordinal
                for xml path('')
            )
            , 3
            , 10000
        )    as [Normal]    
        , substring
        (
            (
                select ', ' + co.[name]
                from sys.index_columns ic
                join sys.columns co on co.object_id = i.object_id and co.column_id = ic.column_id
                where ic.object_id = i.object_id and ic.index_id = i.index_id and ic.is_included_column = 1
                order by ic.key_ordinal
                for xml path('')
            )
            , 3
            , 10000
        )    as [Included]    

) Columns
where o.[type] = 'U' --USER_TABLE
order by o.[name], i.[name], i.is_primary_key desc

sELECT 
     TableName = t.name,
     IndexName = ind.name,
     --IndexId = ind.index_id,
     ColumnId = ic.index_column_id,
     ColumnName = col.name,
     key_ordinal,
     ind.type_desc
     --ind.*,
     --ic.*,
     --col.* 
FROM 
     sys.indexes ind 
INNER JOIN 
     sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id 
INNER JOIN 
     sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id 
INNER JOIN 
     sys.tables t ON ind.object_id = t.object_id 
WHERE 
     ind.is_primary_key = 0 
     AND ind.is_unique = 0 
     AND ind.is_unique_constraint = 0 
     AND t.is_ms_shipped = 0 
     and t.name='CompanyReconciliation' --table name
     and key_ordinal>0
ORDER BY 
     t.name, ind.name, ind.index_id, ic.index_column_id 

以下工作在SQL Server 2014/2016以及任何Microsoft Azure SQL数据库。

生成一个全面的结果集，可以很容易地导出到Notepad/Excel中进行切片和切块

表名 索引名称 指数描述 索引列-按顺序 包括列-按顺序

 SELECT '[' + s.NAME + '].[' + o.NAME + ']' AS 'table_name'
    ,+ i.NAME AS 'index_name'
    ,LOWER(i.type_desc) + CASE 
        WHEN i.is_unique = 1
            THEN ', unique'
        ELSE ''
        END + CASE 
        WHEN i.is_primary_key = 1
            THEN ', primary key'
        ELSE ''
        END AS 'index_description'
    ,STUFF((
            SELECT ', [' + sc.NAME + ']' AS "text()"
            FROM syscolumns AS sc
            INNER JOIN sys.index_columns AS ic ON ic.object_id = sc.id
                AND ic.column_id = sc.colid
            WHERE sc.id = so.object_id
                AND ic.index_id = i1.indid
                AND ic.is_included_column = 0
            ORDER BY key_ordinal
            FOR XML PATH('')
            ), 1, 2, '') AS 'indexed_columns'
    ,STUFF((
            SELECT ', [' + sc.NAME + ']' AS "text()"
            FROM syscolumns AS sc
            INNER JOIN sys.index_columns AS ic ON ic.object_id = sc.id
                AND ic.column_id = sc.colid
            WHERE sc.id = so.object_id
                AND ic.index_id = i1.indid
                AND ic.is_included_column = 1
            FOR XML PATH('')
            ), 1, 2, '') AS 'included_columns'
FROM sysindexes AS i1
INNER JOIN sys.indexes AS i ON i.object_id = i1.id
    AND i.index_id = i1.indid
INNER JOIN sysobjects AS o ON o.id = i1.id
INNER JOIN sys.objects AS so ON so.object_id = o.id
    AND is_ms_shipped = 0
INNER JOIN sys.schemas AS s ON s.schema_id = so.schema_id
WHERE so.type = 'U'
    AND i1.indid < 255
    AND i1.STATUS & 64 = 0 --index with duplicates
    AND i1.STATUS & 8388608 = 0 --auto created index
    AND i1.STATUS & 16777216 = 0 --stats no recompute
    AND i.type_desc <> 'heap'
    AND so.NAME <> 'sysdiagrams'
ORDER BY table_name
    ,index_name;