sql-server - SQL Server 查询查找聚集索引

Question

是否可以编写一个查询来返回具有不基于身份键的聚集索引的所有表？

Answer 1

这个怎么样:

SELECT
    TableName = t.name, 
    ClusteredIndexName = i.name,
    ColumnName = c.Name
FROM
    sys.tables t
INNER JOIN 
    sys.indexes i ON t.object_id = i.object_id
INNER JOIN 
    sys.index_columns ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id
INNER JOIN 
    sys.columns c ON ic.column_id = c.column_id AND ic.object_id = c.object_id
WHERE
    i.index_id = 1  -- clustered index
    AND c.is_identity = 0
    AND EXISTS (SELECT * 
                FROM sys.columns c2 
                WHERE ic.object_id = c2.object_id AND c2.is_identity = 1)

好的，此查询将列出那些具有不是身份列的主键，但主键约束中还有一个是的第二列IDENTITY 列。