Monthly Archives: July 2011

Find the tables without primary Key

While working in any of the RDBMS it is mandatory to have clustered indexes in each of the tables. It makes sense, helps performance, and in most cases is the best starting point for a table.

Now the tedious job is to find the table names from existing database in which primary key (clustered index) is not implemented, the manual process is to right click on each of the table and go in design view to check it.

We can also do it by executing below T-SQL in the database; it will show the table names in which primary key are not implemented.

SELECT SCHEMA_NAME(o.schema_id) AS [schema]

    ,object_name(i.object_id ) AS [table]









FROM sys.indexes i

    INNER JOIN sys.objects o ON i.object_id = o.object_id

INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id

LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id

WHERE i.type_desc = ‘HEAP’

ORDER BY rows desc





Prashant Deshpande


Leave a comment

Filed under Uncategorized