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

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

有两个“sys”目录视图可以参考:Indexes和sys.index_columns。

这些会给你关于下标和它们的列的任何信息。

编辑:这个查询非常接近你所寻找的:

SELECT 
     TableName = t.name,
     IndexName = ind.name,
     IndexId = ind.index_id,
     ColumnId = ic.index_column_id,
     ColumnName = col.name,
     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 
ORDER BY 
     t.name, ind.name, ind.index_id, ic.is_included_column, ic.key_ordinal;

——简短而甜蜜:

SELECT OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) AS [Schema],  
  T.[name] AS [table_name], I.[name] AS [index_name], AC.[name] AS [column_name],  
  I.[type_desc], I.[is_unique], I.[data_space_id], I.[ignore_dup_key], I.[is_primary_key], 
  I.[is_unique_constraint], I.[fill_factor],    I.[is_padded], I.[is_disabled], I.[is_hypothetical], 
  I.[allow_row_locks], I.[allow_page_locks], IC.[is_descending_key], IC.[is_included_column] 
FROM sys.[tables] AS T  
  INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]  
  INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id] 
  INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id] AND IC.[column_id] = AC.[column_id] 
WHERE T.[is_ms_shipped] = 0 AND I.[type_desc] <> 'HEAP' 
ORDER BY T.[name], I.[index_id], IC.[key_ordinal]   

以上这些方法都不能满足我的需求，但下面这个方法可以:

-- KDF9's concise index list for SQL Server 2005+  (see below for 2000)
--   includes schemas and primary keys, in easy to read format
--   with unique, clustered, and all ascending/descendings in a single column
-- Needs simple manual add or delete to change maximum number of key columns
--   but is easy to understand and modify, with no UDFs or complex logic
--
SELECT
  schema_name(schema_id) as SchemaName, OBJECT_NAME(si.object_id) as TableName, si.name as IndexName,
  (CASE is_primary_key WHEN 1 THEN 'PK' ELSE '' END) as PK,
  (CASE is_unique WHEN 1 THEN '1' ELSE '0' END)+' '+
  (CASE si.type WHEN 1 THEN 'C' WHEN 3 THEN 'X' ELSE 'B' END)+' '+  -- B=basic, C=Clustered, X=XML
  (CASE INDEXKEY_PROPERTY(si.object_id,index_id,1,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+
  (CASE INDEXKEY_PROPERTY(si.object_id,index_id,2,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+
  (CASE INDEXKEY_PROPERTY(si.object_id,index_id,3,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+
  (CASE INDEXKEY_PROPERTY(si.object_id,index_id,4,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+
  (CASE INDEXKEY_PROPERTY(si.object_id,index_id,5,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+
  (CASE INDEXKEY_PROPERTY(si.object_id,index_id,6,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+
  '' as 'Type',
  INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,1) as Key1,
  INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,2) as Key2,
  INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,3) as Key3,
  INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,4) as Key4,
  INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,5) as Key5,
  INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,6) as Key6
FROM sys.indexes as si
LEFT JOIN sys.objects as so on so.object_id=si.object_id
WHERE index_id>0 -- omit the default heap
  and OBJECTPROPERTY(si.object_id,'IsMsShipped')=0 -- omit system tables
  and not (schema_name(schema_id)='dbo' and OBJECT_NAME(si.object_id)='sysdiagrams') -- omit sysdiagrams
ORDER BY SchemaName,TableName,IndexName

-------------------------------------------------------------------
-- or to generate creation scripts put a simple wrapper around that
SELECT SchemaName, TableName, IndexName,
  (CASE pk
    WHEN 'PK' THEN 'ALTER '+
     'TABLE '+SchemaName+'.'+TableName+' ADD CONSTRAINT '+IndexName+' PRIMARY KEY'+
     (CASE substring(Type,3,1) WHEN 'C' THEN ' CLUSTERED' ELSE '' END)
    ELSE 'CREATE '+
     (CASE substring(Type,1,1) WHEN '1' THEN 'UNIQUE ' ELSE '' END)+
     (CASE substring(Type,3,1) WHEN 'C' THEN 'CLUSTERED ' ELSE '' END)+
     'INDEX '+IndexName+' ON '+SchemaName+'.'+TableName
    END)+
  ' ('+
    (CASE WHEN Key1 is null THEN '' ELSE      Key1+(CASE substring(Type,4+1,1) WHEN 'D' THEN ' DESC' ELSE '' END) END)+
    (CASE WHEN Key2 is null THEN '' ELSE ', '+Key2+(CASE substring(Type,4+2,1) WHEN 'D' THEN ' DESC' ELSE '' END) END)+
    (CASE WHEN Key3 is null THEN '' ELSE ', '+Key3+(CASE substring(Type,4+3,1) WHEN 'D' THEN ' DESC' ELSE '' END) END)+
    (CASE WHEN Key4 is null THEN '' ELSE ', '+Key4+(CASE substring(Type,4+4,1) WHEN 'D' THEN ' DESC' ELSE '' END) END)+
    (CASE WHEN Key5 is null THEN '' ELSE ', '+Key5+(CASE substring(Type,4+5,1) WHEN 'D' THEN ' DESC' ELSE '' END) END)+
    (CASE WHEN Key6 is null THEN '' ELSE ', '+Key6+(CASE substring(Type,4+6,1) WHEN 'D' THEN ' DESC' ELSE '' END) END)+
    ')' as CreateIndex
FROM (
  ...
  ...listing SQL same as above minus the ORDER BY...
  ...
  ) as indexes
ORDER BY SchemaName,TableName,IndexName

----------------------------------------------------------
-- For SQL Server 2000 the following should work
--   change table names to sysindexes and sysobjects (no dots)
--   change object_id => id, index_id => indid,
--   change is_primary_key => (select count(constid) from sysconstraints as sc where sc.id=si.id and sc.status&15=1)
--   change is_unique => INDEXPROPERTY(si.id,si.name,'IsUnique')
--   change si.type => INDEXPROPERTY(si.id,si.name,'IsClustered')
--   remove all references to schemas including schema name qualifiers, and the XML type
--   add select where indid<255 and si.status&64=0 (to omit the text/image index and autostats)

如果您的名称包含空格，请在创建脚本中在空格周围添加方括号。

当最后一个Key列全部为空时，您知道没有缺失。

过滤掉原始请求中的主键等是微不足道的。

注意:注意这个解决方案，因为它没有区分索引列和包含列。

使用SQL Server 2016，这给出了所有索引的完整列表，并包含每个表的转储，以便您可以查看表之间的关系。它还显示包含在覆盖索引中的列:

select t.name TableName, i.name IdxName, c.name ColName
    , ic.index_column_id ColPosition
    , i.type_desc Type
    , case when i.is_primary_key = 1 then 'Yes' else '' end [Primary?]
    , case when i.is_unique = 1 then 'Yes' else '' end [Unique?]
    , case when ic.is_included_column = 0 then '' else 'Yes - Included' end [CoveredColumn?]
    , 'indexes >>>>' [*indexes*], i.*, 'index_columns >>>>' [*index_columns*]
    , ic.*, 'tables >>>>' [*tables*]
    , t.*, 'columns >>>>' [*columns*], c.*
from sys.index_columns ic
join sys.tables t on t.object_id = ic.object_id
join sys.columns c on c.object_id = t.object_id and c.column_id = ic.column_id
join sys.indexes i on i.object_id = t.object_id and i.index_id = ic.index_id
order by TableName, IdxName, ColPosition

我需要得到特定的索引，它们的索引列和包含的列。以下是我使用的查询:

SELECT INX.[name] AS [Index Name]
      ,TBL.[name] AS [Table Name]
      ,DS1.[IndexColumnsNames]
      ,DS2.[IncludedColumnsNames]
FROM [sys].[indexes] INX
INNER JOIN [sys].[tables] TBL
    ON INX.[object_id] = TBL.[object_id]
CROSS APPLY 
(
    SELECT STUFF
    (
        (
            SELECT ' [' + CLS.[name] + ']'
            FROM [sys].[index_columns] INXCLS
            INNER JOIN [sys].[columns] CLS 
                ON INXCLS.[object_id] = CLS.[object_id] 
                AND INXCLS.[column_id] = CLS.[column_id]
            WHERE INX.[object_id] = INXCLS.[object_id] 
                AND INX.[index_id] = INXCLS.[index_id]
                AND INXCLS.[is_included_column] = 0
            FOR XML PATH('')
        )
        ,1
        ,1
        ,''
    ) 
) DS1 ([IndexColumnsNames])
CROSS APPLY 
(
    SELECT STUFF
    (
        (
            SELECT ' [' + CLS.[name] + ']'
            FROM [sys].[index_columns] INXCLS
            INNER JOIN [sys].[columns] CLS 
                ON INXCLS.[object_id] = CLS.[object_id] 
                AND INXCLS.[column_id] = CLS.[column_id]
            WHERE INX.[object_id] = INXCLS.[object_id] 
                AND INX.[index_id] = INXCLS.[index_id]
                AND INXCLS.[is_included_column] = 1
            FOR XML PATH('')
        )
        ,1
        ,1
        ,''
    ) 
) DS2 ([IncludedColumnsNames])