Find All Tables Without A Primary Key

The following query will find all the tables in your database which are missing a primary key.  In almost any situation a missing primary key is the result of an oversight or a design error and should be corrected.  Since the INFORMATION_SCHEMA views are being utilized for this query it should be compatible with SQL Server 2000, SQL Server 2005, and SQL Server 2008. Query is as follows:

SELECT
t.TABLE_CATALOG
,t.TABLE_SCHEMA
,t.TABLE_NAME
,t.TABLE_TYPE
FROM
INFORMATION_SCHEMA.TABLES t
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk
ON  t.TABLE_CATALOG = pk.TABLE_CATALOG
AND t.TABLE_NAME = pk.TABLE_NAME
AND t.TABLE_SCHEMA = pk.TABLE_SCHEMA
AND pk.CONSTRAINT_TYPE = 'PRIMARY KEY'
WHERE
pk.TABLE_NAME IS NULL
AND t.TABLE_TYPE='BASE TABLE'
ORDER BY
t.TABLE_CATALOG
,t.TABLE_SCHEMA
,t.TABLE_NAME

Published
Categorized as Scripts

By Phil Steffek

Phil Steffek is a professional sumo wrestler who is hoping to represent the U.S. in the next Olympics. During the off season Phil is a Data Architect working on transactional databases, data warehouse implementations, and all aspects of business intelligence. Also, Phil is not really a sumo wrestler. You can get to know a bit more about my non-sumo doings by checking out my LinkedIn Page

Leave a comment

Your email address will not be published. Required fields are marked *