sql-server - 列出包含列的所有索引(猴子)

Question

我尝试过sp_helpindex，但它显示的是具有索引的列，而不是包含的列。请告诉我如何列出包含列(非键)的所有索引？

Answer 1

针对目录 View 尝试此 T-SQL 查询:

SELECT 
    IndexName = i.Name,
    ColName = c.Name
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 c.column_id = ic.column_id
WHERE
    ic.is_included_column = 1
ORDER BY 
    i.Name

它基本上检查所有索引 (sys.indexes)，然后链接到它们的列并检查哪些列被设计为包含列 (ic.is_included_column = 1 )，然后列出所有这些索引和所有这些列。