这是可行的:

DECLARE @IndexInfo  TABLE (index_name         varchar(250)
                          ,index_description  varchar(250)
                          ,index_keys         varchar(250)
                          )

INSERT INTO @IndexInfo
exec sp_msforeachtable 'sp_helpindex ''?'''
select * from @IndexInfo

这不会返回表名，你会得到所有没有索引的表的警告，如果这是一个问题，你可以在有索引的表上创建一个循环，像这样:

DECLARE @IndexInfoTemp  TABLE (index_name         varchar(250)
                              ,index_description  varchar(250)
                              ,index_keys         varchar(250)
                              )

DECLARE @IndexInfo  TABLE (table_name         sysname
                          ,index_name         varchar(250)
                          ,index_description  varchar(250)
                          ,index_keys         varchar(250)
                          )

DECLARE @Tables Table (RowID       int not null identity(1,1)
                      ,TableName   sysname 
                      )
DECLARE @MaxRow       int
DECLARE @CurrentRow   int
DECLARE @CurrentTable sysname

INSERT INTO @Tables
    SELECT
        DISTINCT t.name 
        FROM sys.indexes i
            INNER JOIN sys.tables t ON i.object_id = t.object_id
        WHERE i.Name IS NOT NULL
SELECT @MaxRow=@@ROWCOUNT,@CurrentRow=1

WHILE @CurrentRow<=@MaxRow
BEGIN

    SELECT @CurrentTable=TableName FROM @Tables WHERE RowID=@CurrentRow

    INSERT INTO @IndexInfoTemp
    exec sp_helpindex @CurrentTable

    INSERT INTO @IndexInfo
            (table_name   , index_name , index_description , index_keys)
        SELECT
            @CurrentTable , index_name , index_description , index_keys
        FROM @IndexInfoTemp

    DELETE FROM @IndexInfoTemp

    SET @CurrentRow=@CurrentRow+1

END --WHILE
SELECT * from @IndexInfo

编辑 如果你愿意，你可以过滤数据，这里有一些例子(这两种方法都适用):

SELECT * FROM @IndexInfo WHERE index_description NOT LIKE '%primary key%'
SELECT * FROM @IndexInfo WHERE index_description NOT LIKE '%nonclustered%' AND index_description  LIKE '%clustered%'
SELECT * FROM @IndexInfo WHERE index_description LIKE '%unique%'

首先，请注意，以上所有查询都可能遗漏或错误地合并索引的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的函数添加到其中，那么就会有许多级联聚合。

我没有经过，但是我在原作者发布的查询中得到了我想要的东西。

我使用它(没有条件/过滤器)来满足我的需求，但它给出了不正确的结果

主要问题是在index_id上没有连接条件的情况下得到叉乘

SELECT S.NAME SCHEMA_NAME,T.NAME TABLE_NAME,I.NAME INDEX_NAME,C.NAME 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
       INNER JOIN SYS.COLUMNS C
    ON C.OBJECT_ID  = T.OBJECT_ID
   **AND IC.INDEX_ID    = I.INDEX_ID**
   AND IC.COLUMN_ID = C.COLUMN_ID
 WHERE 1=1

ORDER BY I.NAME,I.INDEX_ID,IC.KEY_ORDINAL

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

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 
    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

有两个“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;