solving sql server problems for millions of dbas and developers since 2006

SQL Product Highlight

Idera - SQL diagnostic manager

SQL diagnostic manager is a powerful performance monitoring and diagnostics solution that proactively alerts administrators to health, performance or availability problems within their SQL Server environment via a central console or mobile device. SQL diagnostic manager minimizes costly server downtime by providing agent-less, real-time monitoring and customizable alerting for fast diagnosis and remediation of SQL Server performance and availability problems. SQL diagnostic manager also provides a 'community' environment where, using Idera's IntelliFeed(TM) technology DBAs form a community to collaborate to quickly resolve problems, perform maintenance and capture best practices.

Learn more!

Finding primary keys and missing primary keys in SQL Server

By: Greg Robidoux | Read Comments | Print

Greg is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

Related Tips: More

Problem
One design aspect that all tables should have is a primary key. The primary key is the main entry way into your dataset, so that when you access your data you are guaranteed to only affect one row of data. Having primary keys are not only a good design feature they also play an important role in replication and data updates especially when there may be duplicate rows of data. So how can you determine what tables have primary keys and what tables do not have primary keys?

Solution
As mentioned above, primary keys guarantee a unique row of data in your table. Some of the design aspects of a primary key are as follows:

can be one or more columns
column values can not be null
the column or combination of columns must be unique
there can only be one primary key on a table

In the past there have been other tips that focus on all indexes that exist in the database, but here we will take a different look at tables that have primary keys and tables that do not have primary keys. For SQL 2005 this is pretty easy to do now ,by using the sys.key_constraints catalog views, but with SQL 2000 it is a bit cryptic to get this information.

Query 1 - Tables with primary keys

SQL 2005

SELECT c.name, b.name, a.name  
FROM sys.key_constraints a  
INNER JOIN sys.tables b ON a.parent_object_id = b.OBJECT_ID  
INNER JOIN sys.schemas c ON a.schema_id = c.schema_id  
WHERE a.type = 'PK'

SQL 2000

SELECT c.name, a.name, b.name 
FROM sysobjects a 
INNER JOIN sysindexes b ON a.id = b.id 
INNER JOIN sysusers c ON a.uid = c.uid 
WHERE (b.status & 2048)<>0

Query 2 - Tables without primary keys

SQL 2005

SELECT c.name, b.name  
FROM sys.tables b  
INNER JOIN sys.schemas c ON b.schema_id = c.schema_id  
WHERE b.type = 'U'  
AND NOT EXISTS 
(SELECT a.name  
FROM sys.key_constraints a  
WHERE a.parent_object_id = b.OBJECT_ID  
AND a.schema_id = c.schema_id  
AND a.type = 'PK' )

SQL 2000

SELECT c.name, a.name 
FROM sysobjects a 
INNER JOIN sysusers c ON a.uid = c.uid 
WHERE xtype = 'U' 
AND NOT EXISTS 
(SELECT b.name 
FROM sysindexes b 
WHERE a.id = b.id 
AND (b.status & 2048)<>0)

Next Steps

Now that you have these queries take the time to identify where primary keys may be missing
As a good design rule you should always create a primacy key for all of your tables
If you don't have a good candidate for a primary key in your table then look at using an identity column just for this purpose

Related Tips: More | Become a paid author

Last Update: 5/4/2007

Share:

Free SQL Server Learning

Comments and Feedback:

Sponsor Information

Try the free performance monitoring tool from Idera!

Time-strapped database professionals use SQL Monitor to look after their servers. Try it online.

SQL Servers keeping you up at night? Contact the Edgewood SQL Server DBAs for a Health Check. Prices start at $995.

Get SQL Server Tips Straight from Kevin Kline.

Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now.

The SQL Server Security THREAT - It’s Closer Than You Think