Find all tables in a database without a primary key

If you are performing a database audit and want to quickly find out the tables not having a primary key, use this query :

USE [Your DB]

GO
SELECT TABLE_SCHEMA as 'SCHEMA', TABLE_NAME as 'NO_Primary_Key'
FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_NAME NOT IN
( SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
) AND TABLE_TYPE = 'BASE TABLE'

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook