Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Finding primary keys and missing primary keys in SQL Server


By:   |   Last Updated: 2007-05-04   |   Comments (1)   |   Related Tips: More > Constraints

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.nameb.namea.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.namea.nameb.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.nameb.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.namea.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


Last Updated: 2007-05-04


get scripts

next tip button



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

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, October 10, 2017 - 4:03:10 AM - nickhac Back To Top

If you want to create Primary Keys on all tables in SQL Server that don't have PKs, this SQL will generate all the PK create code for all the tables

I use this on databases i inherit that are missing lots of keys...

Hope this is of use!

 

@nickhac

 

select * ,

'ALTER TABLE ' +  SchemaName + '.' + Tablename + ' ADD CONSTRAINT PK_'  + Tablename +  ' PRIMARY KEY  CLUSTERED (' +Table_Column_FirstWithIDInName + ')' ScriptToCreatePrimaryKey

from (

SELECT OBJECT_SCHEMA_NAME(OBJECT_ID) SchemaName, OBJECT_NAME(OBJECT_ID) AS Tablename,
(SELECT top 1 Column_Name FROM INFORMATION_SCHEMA.COLUMNS  where table_name = T.name and column_name like '%ID%' order by Ordinal_Position) Table_Column_FirstWithIDInName

FROM SYS.Tables T
WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey')  = 0  AND type = 'U'
) TblsWithoutPK

    


Learn more about SQL Server tools