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]

,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

Leave a comment

Filed under Uncategorized