根据公认的答案和另外两个问题1,2，我整理了以下问题:

SELECT
    QUOTENAME(t.name) AS TableName,
    QUOTENAME(i.name) AS IndexName,
    i.is_primary_key,
    i.is_unique,
    i.is_unique_constraint,
    STUFF(REPLACE(REPLACE((
        SELECT QUOTENAME(c.name) + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE '' END AS [data()]
        FROM sys.index_columns AS ic
        INNER JOIN sys.columns AS 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
        ORDER BY ic.key_ordinal
        FOR XML PATH
    ), '<row>', ', '), '</row>', ''), 1, 2, '') AS KeyColumns,
    STUFF(REPLACE(REPLACE((
        SELECT QUOTENAME(c.name) AS [data()]
        FROM sys.index_columns AS ic
        INNER JOIN sys.columns AS 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
        ORDER BY ic.index_column_id
        FOR XML PATH
    ), '<row>', ', '), '</row>', ''), 1, 2, '') AS IncludedColumns,
    u.user_seeks,
    u.user_scans,
    u.user_lookups,
    u.user_updates
FROM sys.tables AS t
INNER JOIN sys.indexes AS i ON t.object_id = i.object_id
LEFT JOIN sys.dm_db_index_usage_stats AS u ON i.object_id = u.object_id AND i.index_id = u.index_id
WHERE t.is_ms_shipped = 0
AND i.type <> 0

该查询返回如下所示的结果，其中显示了索引的列表、它们的列和用法。非常有助于确定哪个指数比其他指数表现更好:

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

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

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

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

我更新了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

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