This is a companion post to my earlier post Finding Tables Without Referential Integrity. People have since asked me how to systematically identify all tables that don’t have a primary key. Here’s a simple query to do that for SQL Server 2005 and above:
select s.name as SchemaName, t.name as TableName
from sys.objects t
inner join sys.schemas s
on t.schema_id = s.schema_id
where t.type = 'U'
and not exists (
select 1
from sys.objects pk
where pk.type = 'PK'
and t.object_id = pk.parent_object_id
)
Here's a quick review of SQL Server metadata:
sys.objects and sys.schemas are metadata system views new as of SQL Server 2005.
sys.objects is a replacement for the deprecated SQL Server 2000 system table sysobjects.
sys.objects is for user defined objects.
sys.sysobjects is for system objects.
sys.all_objects is the union of sys.objects and sys.sysobjects.