当我有这个需求时，我使用了以下查询…

SELECT 
    TableName = t.name,
    ColumnId = col.column_id, 
    ColumnName = col.name,
    DataType = ty.name,
    MaxSize = ty.max_length,
    IsNullable = CASE WHEN (col.is_nullable = 1) THEN 'Y' END,
    IsIdentity = CASE WHEN (col.is_identity = 1) THEN 'Y' END,
    IsPrimaryKey = CASE WHEN (ic.column_id = col.column_id) THEN 'Y' END,
    IsForeignKey = CASE WHEN (fkc.parent_column_id = col.column_id) THEN 'Y' END,
    IsDefault = CASE WHEN (dc.parent_column_id = col.column_id) THEN 'Y' END
FROM 
    sys.tables t
INNER JOIN 
     sys.columns col ON t.object_id = col.object_id 
LEFT JOIN
    sys.indexes ind ON t.object_id = ind.object_id 
LEFT JOIN 
     sys.index_columns ic ON ic.index_id=ind.index_id AND ic.object_id = col.object_id and ic.column_id = col.column_id
LEFT JOIN sys.foreign_key_columns fkc
                ON fkc.parent_object_id = col.object_id AND fkc.parent_column_id=col.column_id
LEFT JOIN sys.default_constraints dc
                ON dc.parent_object_id = col.object_id AND dc.parent_column_id=col.column_id
LEFT JOIN
     sys.types ty on ty.user_type_id = col.user_type_id

WHERE
    --t.name='<TABLENAME>'
    t.schema_id = 10    --SCHEMA ID
    AND ind.is_primary_key=1    
ORDER BY
    t.name, ColumnId

下面的查询包含了所有列的用户定义索引的所有相关信息(对于唯一约束和主键没有索引):

SELECT I.name as IndexName, 
        CASE WHEN I.is_unique = 1 THEN 'Yes' ELSE 'No' END as 'Unique',
        I.type_desc COLLATE DATABASE_DEFAULT as Index_Type,
        '[' + SCHEMA_NAME(T.schema_id) + ']' as 'Schema',
        '[' + T.name + ']' as TableName,
        STUFF((SELECT ', [' + C.name + CASE WHEN IC.is_descending_key = 0 THEN '] ASC' ELSE '] DESC' END
            FROM sys.index_columns IC INNER JOIN sys.columns C ON  IC.object_id = C.object_id  AND IC.column_id = C.column_id
            WHERE IC.is_included_column = 0 AND IC.object_id = I.object_id AND IC.index_id = I.Index_id
            FOR XML PATH('')), 1, 2, '') as Key_Columns,
        Included_Columns, 
        I.filter_definition,
        CASE WHEN I.is_padded = 1 THEN 'ON' ELSE 'OFF' END as PAD_INDEX, 
        CASE WHEN ST.no_recompute = 0 THEN 'OFF' ELSE 'ON' END as [Statistics_Norecompute],
        CONVERT(VARCHAR(5), CASE WHEN I.fill_factor = 0 THEN 100 ELSE I.fill_factor END) as [Fillfactor],
        CASE WHEN I.ignore_dup_key = 1 THEN 'ON' ELSE 'OFF' END as [Ignore_Dup_Key],       
        CASE WHEN I.allow_row_locks = 1 THEN 'ON' ELSE 'OFF' END as [Allow_Row_Locks], 
        CASE WHEN I.allow_page_locks = 1 THEN 'ON' ELSE 'OFF' END [Allow_Page_Locks]        
FROM    sys.indexes I INNER JOIN        
        sys.tables T ON  T.object_id = I.object_id INNER JOIN       
        sys.stats ST ON  ST.object_id = I.object_id AND ST.stats_id = I.index_id INNER JOIN 
        sys.data_spaces DS ON  I.data_space_id = DS.data_space_id INNER JOIN 
        sys.filegroups FG ON  I.data_space_id = FG.data_space_id LEFT OUTER JOIN 
        (SELECT * FROM 
            (SELECT IC2.object_id, IC2.index_id,
                STUFF((SELECT ', ' + C.name FROM sys.index_columns IC1 INNER JOIN 
                    sys.columns C ON C.object_id = IC1.object_id
                        AND C.column_id = IC1.column_id
                        AND IC1.is_included_column = 1
                    WHERE  IC1.object_id = IC2.object_id AND IC1.index_id = IC2.index_id
                    GROUP BY IC1.object_id, C.name, index_id  FOR XML PATH('')
                ), 1, 2, '') as Included_Columns
            FROM sys.index_columns IC2
            GROUP BY IC2.object_id, IC2.index_id) tmp1
            WHERE Included_Columns IS NOT NULL
        ) tmp2
        ON tmp2.object_id = I.object_id AND tmp2.index_id = I.index_id
WHERE I.is_primary_key = 0 AND I.is_unique_constraint = 0;

作为额外的奖励，下面的查询被格式化为写出创建索引和删除索引脚本:

SELECT I.name as IndexName, 
        -- Uncommnent line below to include checking for index exists as part of the script
        --'IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = '''+ I.name +''') ' +
        'CREATE ' + CASE WHEN I.is_unique = 1 THEN ' UNIQUE ' ELSE '' END +
        I.type_desc COLLATE DATABASE_DEFAULT + ' INDEX [' +
        I.name + '] ON [' + SCHEMA_NAME(T.schema_id) + '].[' + T.name + '] (' + STUFF(
        (SELECT ', [' + C.name + CASE WHEN IC.is_descending_key = 0 THEN '] ASC' ELSE '] DESC' END
            FROM sys.index_columns IC INNER JOIN sys.columns C ON  IC.object_id = C.object_id  AND IC.column_id = C.column_id
            WHERE IC.is_included_column = 0 AND IC.object_id = I.object_id AND IC.index_id = I.Index_id
            FOR XML PATH('')), 1, 2, '')  + ') ' +
        ISNULL(' INCLUDE (' + IncludedColumns + ') ', '') +
        ISNULL(' WHERE ' + I.filter_definition, '') + 
        'WITH (PAD_INDEX = ' + CASE WHEN I.is_padded = 1 THEN 'ON' ELSE 'OFF' END + 
        ', STATISTICS_NORECOMPUTE = ' + CASE WHEN ST.no_recompute = 0 THEN 'OFF' ELSE 'ON' END + 
        ', SORT_IN_TEMPDB = OFF' + 
        ', FILLFACTOR = ' + CONVERT(VARCHAR(5), CASE WHEN I.fill_factor = 0 THEN 100 ELSE I.fill_factor END) +
        ', IGNORE_DUP_KEY = ' + CASE WHEN I.ignore_dup_key = 1 THEN 'ON' ELSE 'OFF' END +      
        ', ONLINE = OFF' + 
        ', ALLOW_ROW_LOCKS = ' + CASE WHEN I.allow_row_locks = 1 THEN 'ON' ELSE 'OFF' END + 
        ', ALLOW_PAGE_LOCKS = ' + CASE WHEN I.allow_page_locks = 1 THEN 'ON' ELSE 'OFF' END + 
        ') ON [' + DS.name + '];' + CHAR(13) + CHAR(10) + 'GO' as [CreateIndex],
        'DROP INDEX ['+ I.name +'] ON ['+ SCHEMA_NAME(T.schema_id) +'].['+ T.name +'];' +
        CHAR(13) + CHAR(10) + 'GO' AS [DropIndex]
FROM    sys.indexes I INNER JOIN        
        sys.tables T ON  T.object_id = I.object_id INNER JOIN       
        sys.stats ST ON  ST.object_id = I.object_id AND ST.stats_id = I.index_id INNER JOIN 
        sys.data_spaces DS ON  I.data_space_id = DS.data_space_id INNER JOIN 
        sys.filegroups FG ON  I.data_space_id = FG.data_space_id LEFT OUTER JOIN 
        (SELECT * FROM 
            (SELECT IC2.object_id, IC2.index_id,
                STUFF((SELECT ', ' + C.name FROM sys.index_columns IC1 INNER JOIN 
                    sys.columns C ON C.object_id = IC1.object_id
                        AND C.column_id = IC1.column_id
                        AND IC1.is_included_column = 1
                    WHERE  IC1.object_id = IC2.object_id AND IC1.index_id = IC2.index_id
                    GROUP BY IC1.object_id, C.name, index_id  FOR XML PATH('')
                ), 1, 2, '') as IncludedColumns
            FROM sys.index_columns IC2
            GROUP BY IC2.object_id, IC2.index_id) tmp1
            WHERE IncludedColumns IS NOT NULL
        ) tmp2
        ON tmp2.object_id = I.object_id AND tmp2.index_id = I.index_id
WHERE I.is_primary_key = 0 AND I.is_unique_constraint = 0 

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

-- 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列全部为空时，您知道没有缺失。

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

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

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

select i.object_id, i.name as [index] , STRING_AGG(c.name,', ') as [column], o.name as [table] from sys.indexes i
INNER join sys.index_columns ic on ic.object_id = i.object_id and ic.index_id = i.index_id
INNER join sys.columns c on c.object_id = ic.object_id and ic.column_id = c.column_id
INNER JOIN sys.objects o on o.object_id = i.object_id
where i.object_id > 100 and i.is_primary_key = 0 and i.is_unique = 0 and o.is_ms_shipped <> 1
group by i.object_id, i.name, o.name
order by i.name

将此用于sql 2016及更高级别，它会显示object_id, indexname，列和表名为非唯一的，没有主键

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