查找表主键和缺少主键的表

 

Finding primary keys and missing primary keys in SQL Server

 http://www.mssqltips.com/tip.asp?tip=1237 

 

Written By: Edgewood Solutions Engineers -- 5/4/2007 -- 0 comments

 

Get new tips every day - Don't miss out - Click to find out 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