T-SQL find tables without primary key

Here’s a T-SQL find tables without primary key.

SELECT OBJECT_SCHEMA_NAME(tables.object_id,db_id()) AS SchemaName,
tables.name As TableName
FROM sys.tables tables join sys.indexes indexes
ON tables.object_id=indexes.object_id
WHERE indexes.is_primary_key=0
GO

The sys.indexes.is_primary_key column indicates whether a table has a primary key or not. The value 1 indicates that table has a primary key column and a value of 0 indicates that table doesn’t have a primary key column.  The output from above query is shown below.

   

1_T-SQL find tables without primary key

 

Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

   

2 Comments on “T-SQL find tables without primary key”

  1. This is not a good example. The list of tables provides includes any table that has an index which is not a primary key. Not the list of tables that don’t have any primary keys. A better approach would be:
    SELECT TableName = SCHEMA_NAME(schema_id) + ‘.’ + [name]
    FROM sys.tables AS T
    WHERE NOT EXISTS (SELECT NULL FROM sys.indexes AS I WHERE I.object_id = T.object_id AND (I.is_primary_key = 1 or I.is_unique = 1))
    ORDER BY TableName;
    This shows ALL the tables where a PRIMARY KEY or UNIQUE index is not defined. regardless of other indexes.

  2. Agree with Ulises Telcontar. Bad SQL. here is another example that just finds those without PKs. I understand why Ulises included unique keys but its a different question than what was originally asked. Also note you can solve by using “not exists” or a left join testing for nulls like this (I tried to make the aliases and form of the query as similar to the original post as I could):

    SELECT OBJECT_SCHEMA_NAME(tables.object_id,db_id()) AS SchemaName,
    tables.name As TableName
    FROM sys.tables tables
    left join sys.indexes indexes ON tables.object_id=indexes.object_id
    and indexes.is_primary_key=1
    where indexes.object_id is null

Leave a Reply

Your email address will not be published.