SQL Server查询以查找聚簇索引 [英] SQL Server query to find clustered indexes

问题描述

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

Is it possible to write a query that returns all tables that have clustered indexes that are not based on an identity key?

推荐答案

这是怎么回事:

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)

好的，此查询将列出那些主键，这些主键的列不是 身份，但在主键约束中还存在第二列 IS IDENTITY列.

OK, this query will list those primary keys that have a column which is not identity, but where there's also additionally a second column in the primary key constraint that IS an IDENTITY column.

这篇关于SQL Server查询以查找聚簇索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！