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]

,p.rows

,user_seeks

,user_scans

,user_lookups

,user_updates

,last_user_seek

,last_user_scan

,last_user_lookup

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

 

 

Thanks

 

Prashant Deshpande

Advertisements

Leave a comment

Filed under Uncategorized

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s