如何显示SQL Server表格索引？

Question

我需要列出/显示表中包含的所有聚集和非聚集索引。

我如何使用SQL Server 2008 R2来实现这个目标？

- Sarfaraz Makandar

Answer 1

这个怎么样：

SELECT 
    TableName = t.Name,
    i.*
FROM 
    sys.indexes i
INNER JOIN 
    sys.tables t ON t.object_id = i.object_id
WHERE
    T.Name = 'YourTableName'

如果您需要更多信息（例如索引中包含的列、它们的数据类型等），您可以将查询扩展为以下内容：

SELECT 
    TableName = t.Name,
    IndexName = i.Name, 
    IndexType = i.type_desc,
    ColumnOrdinal = Ic.key_ordinal,
    ColumnName = c.name,
    ColumnType = ty.name
FROM 
    sys.indexes i
INNER JOIN 
    sys.tables t ON t.object_id = i.object_id
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 c.column_id = ic.column_id
INNER JOIN 
    sys.types ty ON c.system_type_id = ty.system_type_id
WHERE 
    t.name = 'YourTableName' 
ORDER BY
    t.Name, i.name, ic.key_ordinal

这些系统目录视图包含了关于您的系统的丰富信息...

- marc_s

---

或者有用的快捷键 Alt + F1 - Darka