Get a list of tables in SQL Server that don't have primary keys defined

In SQL Server it is a best practice for all tables to have primary keys defined. A primary key is really a constraint. Below is a query to get a list of tables and the name of the primary key contstraint that is associated with that table. If the CONSTRAINT_NAME column is null then it doesn't have a primary key defined.


select t.TABLE_SCHEMA, t.TABLE_NAME, c.CONSTRAINT_NAME
from INFORMATION_SCHEMA.TABLES t 
left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS c 
on (t.TABLE_SCHEMA = c.TABLE_SCHEMA and t.TABLE_NAME = c.TABLE_NAME and t.TABLE_TYPE = 'BASE TABLE' and c.CONSTRAINT_TYPE = 'PRIMARY KEY')
order by TABLE_NAME


You can add a where clause such as

where c.CONSTRAINT_TYPE is null 

to filter the results to just the tables that don't have a primary key.