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.
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
One Comment on “T-SQL find tables without primary key”
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.