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

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

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

这是可行的:

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

根据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]

试试这个:

EXEC sys.sp_helpindex @objname = 'mytable';