SQL Server query to find clustered indexes

How about this:

SELECT    TableName = t.name,     ClusteredIndexName = i.name,    ColumnName = c.NameFROM    sys.tables tINNER JOIN     sys.indexes i ON t.object_id = i.object_idINNER JOIN     sys.index_columns ic ON i.index_id = ic.index_id AND i.object_id = ic.object_idINNER JOIN     sys.columns c ON ic.column_id = c.column_id AND ic.object_id = c.object_idWHERE    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.

SELECT  s.name AS schema_name, o.name AS object_name, i.name AS index_nameFROM    sys.indexes iJOIN    sys.objects o ON i.object_id = o.object_idJOIN    sys.schemas s ON o.schema_id = s.schema_idWHERE   i.type = 1 -- Clustered index--AND       o.is_ms_shipped = 0 -- Uncomment if you want to see only user objectsAND     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_BusinessEntityIDHumanResources EmployeeDepartmentHistory   PK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_DepartmentIDHumanResources EmployeePayHistory          PK_EmployeePayHistory_BusinessEntityID_RateChangeDatePerson         BusinessEntityAddress       PK_BusinessEntityAddress_BusinessEntityID_AddressID_AddressTypeIDPerson         BusinessEntityContact       PK_BusinessEntityContact_BusinessEntityID_PersonID_ContactTypeID

Following query will give you all the user tables, columns, data type, and if the column is part of cluster index it will return column's sequence/order in the crusted index otherwise it will return 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 Cinner  join sysobjects O on O.Id=C.Id and o.xtype='U' -- User Tablesinner join sysusers U on O.Uid=U.UIDinner join systypes T on C.xtype=T.xtypeleft 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.nameorder by U.name