以下是最好的方法:

SELECT sys.tables.object_id, sys.tables.name as table_name, sys.columns.name as column_name, sys.indexes.name as index_name,
sys.indexes.is_unique, sys.indexes.is_primary_key 
FROM sys.tables, sys.indexes, sys.index_columns, sys.columns 
WHERE (sys.tables.object_id = sys.indexes.object_id AND sys.tables.object_id = sys.index_columns.object_id AND sys.tables.object_id = sys.columns.object_id
AND sys.indexes.index_id = sys.index_columns.index_id AND sys.index_columns.column_id = sys.columns.column_id) 
AND sys.tables.name = 'your_table_name'

我更喜欢使用隐式连接，因为它对我来说更容易理解。您可以删除object_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

这是我的，工作在一个默认模式，但它可以很容易地改进 它提供了3列SQLQueries -创建/删除/重建(没有重组)

查询:

SELECT
'CREATE ' + 
CASE WHEN is_primary_key=1 THEN 'CLUSTERED' 
WHEN is_primary_key=0 and is_unique_constraint=0 THEN 'NONCLUSTERED'
WHEN is_primary_key=0 and is_unique_constraint=1 THEN 'UNIQUE' END  
+ ' INDEX ' +
QUOTENAME(i.name) + ' ON ' +
QUOTENAME(t.name) + ' ( '  + 
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, '') + ' ) '  -- keycols
+ COALESCE(' INCLUDE ( ' +
    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, '') + ' ) ',    -- included cols
    '') as [Create],
'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(t.name) as [Drop],
'ALTER INDEX ' + QUOTENAME(i.name)  + ' ON ' +QUOTENAME(t.name) + ' REBUILD ' as [Rebuild]
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
order by QUOTENAME(t.name), is_primary_key desc

输出

Create                                                                                                      Drop                                    Rebuild
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE CLUSTERED INDEX [PK_Table1] ON [Table1] ( [Tab1_ID] )                                                DROP INDEX [PK_Table1] ON [Table1]      ALTER INDEX [PK_Table1] ON [Table1] REBUILD 
CREATE UNIQUE INDEX [IX_Table1_Name] ON [Table1] ( [Tab1_Name] )                                            DROP INDEX [IX_Table1_Name] ON [Table1] ALTER INDEX [IX_Table1_Name] ON [Table1] REBUILD 
CREATE NONCLUSTERED INDEX [IX_Table2] ON [Table2] ( [Tab2_Name], [Tab2_City] )  INCLUDE ( [Tab2_PhoneNo] )  DROP INDEX [IX_Table2] ON [Table2]      ALTER INDEX [IX_Table2] ON [Table2] REBUILD

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

以下是最好的方法:

SELECT sys.tables.object_id, sys.tables.name as table_name, sys.columns.name as column_name, sys.indexes.name as index_name,
sys.indexes.is_unique, sys.indexes.is_primary_key 
FROM sys.tables, sys.indexes, sys.index_columns, sys.columns 
WHERE (sys.tables.object_id = sys.indexes.object_id AND sys.tables.object_id = sys.index_columns.object_id AND sys.tables.object_id = sys.columns.object_id
AND sys.indexes.index_id = sys.index_columns.index_id AND sys.index_columns.column_id = sys.columns.column_id) 
AND sys.tables.name = 'your_table_name'

我更喜欢使用隐式连接，因为它对我来说更容易理解。您可以删除object_id引用，因为您可能不需要它。

欢呼。

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