SQL Server查询以查找聚集索引

Question

是否有可能编写一个查询，返回所有具有聚集索引但不基于标识键的表？

- DevilDog

---

你尝试过什么了吗？元表sys.indexes、sys.index_columns和sys.columns可能是一个好的起点。 - Tony Hopkinson

你能澄清一下吗？你想要所有没有在“仅”IDENTITY列上聚集的表，还是所有不包含IDENTITY列在聚集索引中的表？ - Aaron Bertrand

亚伦，我想我感兴趣的是任何不仅仅是标识键的聚集索引。 - DevilDog

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)

好的，这个查询将列出那些主键包含一个不是自增的列，但是还有另外一个列是主键约束中的 自增 列。

- marc_s

---

Marc，不，我想知道那些由标识键和其他内容组成的聚集索引。 - DevilDog

@DevilDog：我更新了我的回复 - 现在应该能够满足你的要求了。 - marc_s

Answer 2

SELECT  s.name AS schema_name, o.name AS object_name, i.name AS index_name
FROM    sys.indexes i
JOIN    sys.objects o ON i.object_id = o.object_id
JOIN    sys.schemas s ON o.schema_id = s.schema_id
WHERE   i.type = 1 -- Clustered index
--AND       o.is_ms_shipped = 0 -- Uncomment if you want to see only user objects
AND     NOT EXISTS (
    SELECT  * 
    FROM    sys.index_columns ic INNER JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
    WHERE   ic.object_id = i.object_id AND ic.index_id = i.index_id
    AND     c.is_identity = 1 -- Is identity column
)
ORDER BY schema_name, object_name, index_name;

样例输出（AdventureWorks2008R2）：

schema_name    object_name                 index_name
-------------- --------------------------- --------------------------------------------------------------------
HumanResources Employee                    PK_Employee_BusinessEntityID
HumanResources EmployeeDepartmentHistory   PK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_DepartmentID
HumanResources EmployeePayHistory          PK_EmployeePayHistory_BusinessEntityID_RateChangeDate
Person         BusinessEntityAddress       PK_BusinessEntityAddress_BusinessEntityID_AddressID_AddressTypeID
Person         BusinessEntityContact       PK_BusinessEntityContact_BusinessEntityID_PersonID_ContactTypeID

- Bogdan Sahlean

Answer 3

以下查询将为您提供所有用户表、列、数据类型，以及如果该列是群集索引的一部分，则会返回列在群集索引中的顺序/顺序，否则将返回NULL。

SELECT U.name [OWNER],O.name [TABLE_NAME],C.name [COLUMN_NAME],T.name [DATA_TYPE],C.length [DATA_LENGTH], x.keyno [Primary_Key_order]
FROM syscolumns C
inner  join sysobjects O on O.Id=C.Id and o.xtype='U' -- User Tables
inner join sysusers U on O.Uid=U.UID
inner join systypes T on C.xtype=T.xtype
left outer join (Select O.name [TABLE_NAME] , C.name [COLUMN_NAME], IK.keyno
            from syscolumns C
            inner  join sysobjects O on O.Id=C.Id and O.xtype='U' -- User Tables
            join sysindexkeys IK on O.id=IK.ID  and C.colid=IK.COLID and Indid=1 -- Only Clustered Index
            ) x 
            on x.TABLE_NAME=O.name and X.COLUMN_NAME=C.name
order by U.name

- Yuvraj