Recently i was asked to find all the Primary keys that are not clustered Indexes in the database.I constructed the below script which will help us to locate the primary keys in the database which are not clustered index.

Use DatabaseName
go
SELECT idx.name AS IndexName
, fg.name AS Filegroup
, object_name(idx.object_id) AS TableName
, CASE
WHEN index_id = 1 THEN
‘Clustered’
WHEN index_id = 0 THEN
‘heap’
ELSE
‘Non Clustered’
END AS TypeOfIndex
, CASE
WHEN idx.is_primary_key = 1 AND index_id > 1 THEN
‘Primary key as Unique Non Clustered Index’
WHEN idx.is_primary_key = 1 AND index_id = 1 THEN
‘PK and Clustered’
ELSE
‘Unique Non-Cl’
END AS PrimaryKeyAsNonCLIndex
FROM
sys.filegroups fg
JOIN sys.indexes idx
ON fg.data_space_id = idx.data_space_id
JOIN sys.objects O
ON object_name(idx.object_id) = O.name
WHERE
object_name(idx.object_id) NOT LIKE ‘sys%’
AND idx.is_primary_key = 1
AND index_id > 1
ORDER BY
TableName

Tagged: Microsoft SQL Server, Primary Keys, Schema