首先，请注意，以上所有查询都可能遗漏或错误地合并索引的INCLUDE列。在某些情况下，还缺少列的正确排序和/或ASC/DESC选项。

由jona修改了上述查询。顺便说一句，在我使用的许多数据库中，我都安装了自己的CLR CONCATENATE聚合函数，因此下面的代码依赖于存在这样的东西。上面的SQL语句简化为更易于维护:

SELECT
  s.[name] AS [schema_name]
, t.[name] AS [table_name]
, i.[name] AS [index_name]
, dbo.Concatenate(CASE WHEN ic.[key_ordinal] > 0 AND ic.[is_descending_key] = 1 THEN c.[name] + ' DESC' WHEN key_ordinal > 0 THEN c.[name] ELSE NULL END,',',1) AS [columns]
, dbo.Concatenate(CASE WHEN ic.[is_included_column] = 1 THEN c.[name] ELSE NULL END,',',1) AS [includes]
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
GROUP BY
  s.[name]
, t.[name]
, i.[name]
ORDER BY
  s.[name]
, t.[name]
, i.[name]

如果您的环境允许将基于clr的函数添加到其中，那么就会有许多级联聚合。

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 

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

这是@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

根据Tim Ford的代码，这是正确答案:

  select tab.[name]  as [table_name],
         idx.[name]  as [index_name],
         allc.[name] as [column_name],
         idx.[type_desc],
         idx.[is_unique],
         idx.[data_space_id],
         idx.[ignore_dup_key],
         idx.[is_primary_key],
         idx.[is_unique_constraint],
         idx.[fill_factor],
         idx.[is_padded],
         idx.[is_disabled],
         idx.[is_hypothetical],
         idx.[allow_row_locks],
         idx.[allow_page_locks],
         idxc.[is_descending_key],
         idxc.[is_included_column],
         idxc.[index_column_id]

     from sys.[tables] as tab

    inner join sys.[indexes]       idx  on tab.[object_id] =  idx.[object_id]
    inner join sys.[index_columns] idxc on idx.[object_id] = idxc.[object_id] and  idx.[index_id]  = idxc.[index_id]
    inner join sys.[all_columns]   allc on tab.[object_id] = allc.[object_id] and idxc.[column_id] = allc.[column_id]

    where tab.[name] Like '%table_name%'
      and idx.[name] Like '%index_name%'
    order by tab.[name], idx.[index_id], idxc.[index_column_id]

在Oracle中

select CONNECYBY.SCHEMA_NAME,CONNECYBY.TABLE_NAME,CONNECYBY.INDEX_NAME,CONNECYBY.COLUMN_NAME
from (  select TABLE_OWNER SCHEMA_NAME,TABLE_NAME,INDEX_NAME,COLUMN_POSITION,trim(',' from sys_connect_by_path(COLUMN_NAME,',')) COLUMN_NAME
        from DBA_IND_COLUMNS
        start with COLUMN_POSITION = 1
        connect by TABLE_OWNER = prior TABLE_OWNER
        and TABLE_NAME = prior TABLE_NAME
        and INDEX_NAME = prior INDEX_NAME
        and COLUMN_POSITION = prior COLUMN_POSITION + 1) CONNECYBY
join (  select TABLE_OWNER SCHEMA_NAME,TABLE_NAME,INDEX_NAME,max(COLUMN_POSITION) COLUMN_POSITION
        from DBA_IND_COLUMNS
        group by TABLE_OWNER,TABLE_NAME,INDEX_NAME) MAX_CONNECYBY
on (    CONNECYBY.SCHEMA_NAME = MAX_CONNECYBY.SCHEMA_NAME
        and CONNECYBY.TABLE_NAME = MAX_CONNECYBY.TABLE_NAME
        and CONNECYBY.INDEX_NAME = MAX_CONNECYBY.INDEX_NAME
        and CONNECYBY.COLUMN_POSITION = MAX_CONNECYBY.COLUMN_POSITION)
order by CONNECYBY.SCHEMA_NAME,CONNECYBY.TABLE_NAME,CONNECYBY.INDEX_NAME

SQL Server 与

CONNECTBY(SCHEMA_NAME,TABLE_NAME,INDEX_NAME,INDEX_COLUMN_ID,COLUMN_NAME) 
as 
    (   select SCHEMAS.NAME SCHEMA_NAME
            , TABLES.NAME TABLE_NAME
            , INDEXES.NAME INDEX_NAME
            , INDEX_COLUMNS.INDEX_COLUMN_ID INDEX_COLUMN_ID
            , cast(COLUMNS.NAME AS VARCHAR(MAX)) COLUMN_NAME
        from SYS.INDEXES
        join SYS.TABLES on (INDEXES.OBJECT_ID = TABLES.OBJECT_ID)
        join SYS.SCHEMAS on (TABLES.SCHEMA_ID = SCHEMAS.SCHEMA_ID)
        join SYS.INDEX_COLUMNS on ( INDEXES.OBJECT_ID = INDEX_COLUMNS.OBJECT_ID 
                                    and INDEX_COLUMNS.INDEX_ID = INDEXES.INDEX_ID)
        join SYS.COLUMNS on (   INDEXES.OBJECT_ID = COLUMNS.OBJECT_ID 
                                and INDEX_COLUMNS.COLUMN_ID = COLUMNS.COLUMN_ID)
        where INDEX_COLUMNS.INDEX_COLUMN_ID = 1
        union all
        select SCHEMAS.NAME SCHEMA_NAME
            , TABLES.NAME TABLE_NAME
            , INDEXES.NAME INDEX_NAME
            , INDEX_COLUMNS.INDEX_COLUMN_ID INDEX_COLUMN_ID
            , cast(PRIOR.COLUMN_NAME + ',' + COLUMNS.NAME AS VARCHAR(MAX)) COLUMN_NAME
        from SYS.INDEXES
        join SYS.TABLES on (INDEXES.OBJECT_ID = TABLES.OBJECT_ID)
        join SYS.SCHEMAS on (TABLES.SCHEMA_ID = SCHEMAS.SCHEMA_ID)
        join SYS.INDEX_COLUMNS on ( INDEXES.OBJECT_ID = INDEX_COLUMNS.OBJECT_ID 
                                    and INDEX_COLUMNS.INDEX_ID = INDEXES.INDEX_ID)
        join SYS.COLUMNS on (   INDEXES.OBJECT_ID = COLUMNS.OBJECT_ID 
                                and INDEX_COLUMNS.COLUMN_ID = COLUMNS.COLUMN_ID)
        join CONNECTBY as PRIOR on (SCHEMAS.NAME = PRIOR.SCHEMA_NAME 
                                    and TABLES.NAME = PRIOR.TABLE_NAME 
                                    and INDEXES.NAME = PRIOR.INDEX_NAME 
                                    and INDEX_COLUMNS.INDEX_COLUMN_ID = PRIOR.INDEX_COLUMN_ID + 1))
select CONNECTBY.SCHEMA_NAME,CONNECTBY.TABLE_NAME,CONNECTBY.INDEX_NAME,CONNECTBY.COLUMN_NAME
from CONNECTBY
join (  select  SCHEMA_NAME
                , TABLE_NAME
                , INDEX_NAME
                , MAX(INDEX_COLUMN_ID) INDEX_COLUMN_ID
        from CONNECTBY 
        group by SCHEMA_NAME,TABLE_NAME,INDEX_NAME) MAX_CONNECTBY
        on (CONNECTBY.SCHEMA_NAME = MAX_CONNECTBY.SCHEMA_NAME
            and CONNECTBY.TABLE_NAME = MAX_CONNECTBY.TABLE_NAME
            and CONNECTBY.INDEX_NAME = MAX_CONNECTBY.INDEX_NAME
            and CONNECTBY.INDEX_COLUMN_ID = MAX_CONNECTBY.INDEX_COLUMN_ID)
order by CONNECTBY.SCHEMA_NAME,CONNECTBY.TABLE_NAME,CONNECTBY.INDEX_NAME

我更新了KFD9的答案。

我调整了他们的版本，以支持include-specification，而不使用已弃用的indexkey_property

这为索引和约束提供了create和drop语句。

with indexes as (
    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)+' ' as 'Type',  -- B=basic, C=Clustered, X=XML
      (select string_agg(CAST('[' + c.name + ']' + case when is_descending_key = 1 then ' DESC' else '' end AS NVARCHAR(MAX)), ',') within group (order by index_column_id) 
         from sys.index_columns ic JOIN sys.columns c on ic.column_id = c.column_id and ic.object_id = c.object_id where ic.index_id = si.index_id and ic.object_id = si.object_id and ic.is_included_column = 0) Cols,
      (select string_agg(CAST('[' + c.name + ']' + case when is_descending_key = 1 then ' DESC' else '' end AS NVARCHAR(MAX)), ',') within group (order by index_column_id) 
         from sys.index_columns ic JOIN sys.columns c on ic.column_id = c.column_id and ic.object_id = c.object_id where ic.index_id = si.index_id and ic.object_id = si.object_id and ic.is_included_column = 1) IncludedCols,
      (select count(*) from sys.index_columns ic where ic.index_id = si.index_id and ic.object_id = si.object_id) IndexColsCount
    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
)
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)+
  ' ('+Cols+')'+
  isnull(' include ('+IncludedCols+')', '')+
  '' as CreateIndex,
    CASE pk
    WHEN 'PK' THEN 'ALTER '+
     'TABLE ['+SchemaName+'].['+TableName+'] DROP CONSTRAINT ['+IndexName+'] '
    ELSE 'DROP INDEX ['+IndexName+'] ON ['+SchemaName+'].['+TableName + ']'
    END AS DropIndex,
    IndexColsCount
FROM indexes
ORDER BY SchemaName,TableName,IndexName