SQL Server Query to Search for Clustered Indexes

Question

Is it possible to write a query that returns all tables with clustered indexes that are not based on an identification 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 that is not an identifier, but there is also a second primary key constraint column in which IS is an IDENTITY column.

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; 

Output Example (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 

Answer 3

The following query will provide you with all the user tables, columns, data type, and if the column is part of the cluster index, it will return the sequence of columns / order in the encoded 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 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