Find Tables without Clustered Index and with Primary Key in a SQL Server Database

When you create a primary key in SQL Server, it automatically creates a unique, clustered or non-clustered index. If you don't specify clustered or non-clustered for a primary key constraint, SQL Server will create a clustered index if there no clustered index already on the table.

If you want to find tables without Clustered Index (heap) and with Primary Key in a SQL Server Database, you can use the following query:

SELECT DB_NAME() AS Database_Name
, sc.name AS Schema_Name
, o.name AS Table_Name
, pki.name AS Index_Name
, pki.type_desc AS Index_Type
FROM sys.objects o
INNER JOIN sys.indexes pki ON pki.object_id = o.object_id
INNER JOIN sys.indexes cli ON cli.object_id = o.object_id
INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE pki.is_primary_key = 1
AND cli.index_id = 0
AND o.type = 'U'
ORDER BY o.name

The query will find all user tables that have a primary key constraint defined, but no clustered index on a table.

We can see that one of the tables it found is the table dbo.DatabaseLog that has a primary non-clustered key PK_DatabaseLog_DatabaseLogID and the table doesn't have a clustered index:

The second table the query found is Production.ProductProductPhoto that has a primary non-clustered key PK_ProductProductPhoto_ProductID_ProductPhotoID, and the table also doesn't have a clustered index:

It will not show a table where primary key constraint is non-clustered, if there is a clustered index in a table that is not a primary key.

If you want to find tables with non-clustered primary key constraint, that also have a clustered index in a table that is not a primary key, use this query:

SELECT DB_NAME() AS Database_Name
, sc.name AS Schema_Name
, o.name AS Table_Name
, pki.name AS Index_Name
, pki.type_desc AS Index_Type
FROM sys.objects o
INNER JOIN sys.indexes pki ON pki.object_id = o.object_id
INNER JOIN sys.indexes cli ON cli.object_id = o.object_id
INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE (pki.is_primary_key = 1 AND pki.index_id > 1)
AND cli.index_id = 1
AND o.type = 'U'
ORDER BY o.name

The query returned one table Production.BillOfMaterials that has a primary non-clustered key PK_BillOfMaterials_BillOfMaterialsID and the table also has a clustered index that is not a primary key:

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">