Finding primary keys and missing primary keys in SQL Server

By: Greg Robidoux | Comments (1) | Related: > 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, but 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

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 and later this is pretty easy to do by using the sys.key_constraints catalog views, but with SQL 2000 it is a bit cryptic to get this information.

Find All SQL Server Tables in Database With a Primary Key

Here are scripts you can use to find all tables in a database that have a primary key.

SQL Server 2005 / 2008 / 2008 R2 / 2012 / 2014 / 2016 / 2017

SELECT 
   c.name as SchemaName, 
   b.name as TableName, 
   a.name as PKname
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 Server 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

Find All SQL Server Tables in Database Without a Primary Key

Here are scripts you can use to find all tables in a database that do not have a primary key.

SQL Server 2005 / 2008 / 2008 R2 / 2012 / 2014 / 2016 / 2017

SELECT 
   c.name as SchemaName, 
   b.name as TableName
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 Server 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 primary 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

About the author

Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, October 10, 2017 - 4:03:10 AM - nickhac

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