Here is a quick script to list tables in database without primary key.
This is very simple but effective script. It list all the table without primary keys.
USE DatabaseName;
GO
SELECT SCHEMA_NAME(schema_id) AS SchemaName,name AS TableName
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0
ORDER BY SchemaName, TableName;
GO
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
28 Comments. Leave new
when developing a database you can have more then one “Primary Key” in a table
TRUE OR FALSE
im new to sql…….just studying …..can u tell the basic things to be known first
very helpful, thank you very much!
Hi Pinal,
select * from sys.tables where object_id not in (select object_id from sys.indexes where is_primary_key = 1)
This above query also works fine and it returns the expected result.
Please confirm the same.
Thanks,
Ramkumar
@Ramkumar – I think your query is also correct. I don’t have time to test it but it looks OK.
its awesome, good work
Hi Pinal, you should add this script to your Idera white paper.