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

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


One Comment 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.

Leave a Reply

Your email address will not be published.