SQL Server query to find clustered indexes

Question

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

Solution 1

How about this:

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)

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.

Solution 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;

Sample output (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

Comments

[DevilDog]

Marc, no I thing I'd like to know those clustered indexes which were composed of an identity key and something else

[marc_s]

@DevilDog: updated my response - should be handling your requirements now