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