Find Tables Without Primary Keys

From SQLServerPedia

Jump to: navigation, search

See Also: Main_Page - Transact SQL Code Library - Index Performance Tuning

All tables should have clustered indexes. It makes sense, helps performance, and in most cases is the best starting point for a table. If you aren't part of the community of the faithful on clustered indexes, take a moment to read Kimberly L. Tripp's blog on clustered indexes. There are other articles here and there worth reading, but that's the one that seems to help people who aren't already convinced.

When working on a database and you stumble across a table without a primary key, there's probably more than one. This query from Jason Strate's blog entry about heap indexes shows all of the tables with heaps and some of the statistics on the heaps:

T-SQL Script to Find Tables Without Clustered Indexes

SELECT object_name(i.object_id )
    ,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.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 type_desc = 'HEAP'
ORDER BY rows desc

Jason liked pulling in the index usage stats with the indexes to help identify whether the indexes were being used sufficiently enough to make the effort worthwhile. The rowcount helps identify the value of building the indexes as well: the more rows a table has, the more an index will help performance.


Author Credits

Jason Strate

Jason Strate, Digineer Inc, has been a Database Architect for over ten years. His experiences include designing and implementing both OLAP and OLTP solutions involving clustering servers, database monitoring and tuning, analysis services, DTS/Integration Services, Reporting Services, and Notification Services.

Jason is actively involved with the local PASS chapter (SQL Server User Group); he is currently serving on the Executive Board and has presented at monthly meetings. Jason contributed to Microsoft’s published white paper “Empowering Enterprise Solutions with SQL Server 2005 Enterprise Edition and actively blogs about SQL Server and related technologies. Also this year, Jason participated in the development of Microsoft Certification exams for SQL Server 2008, presented at the SSWUG SQL Server Launch Event and is presenting in upcoming SSWUG SQL Server Conference.

His online presences include:

Related Reading

For more information about Jason's query, check out his blog entries:

Retrieved from "http://sqlserverpedia.com/wiki/Find_Tables_Without_Primary_Keys"