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?

Answer 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.