Blog

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

No comments yet.

Leave a Reply