THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

Finding Tables Without Primary Keys

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 as SchemaName, 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.


Published Friday, August 14, 2009 4:33 PM by John Paul Cook

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



aprato said:

Hi John

Wouldn't the following do just as well and you

wouldn't need to read any system tables and it

should work with SQL Server 2000 and above.

select table_name

from information_schema.tables

where objectproperty(object_id(table_name),'TableHasPrimaryKey') = 0

August 14, 2009 7:39 PM

aprato said:

I should amend that to exclude Views

i.e. and table_type = 'base table'

August 14, 2009 7:49 PM

John Paul Cook said:

That's a great alternative, does offer the advantage of working with SQL Server 2000, and it is simpler. I would amend it to also return the schema because the same table name could be in more than one schema.

By the way, I didn't read any system tables, as sys.objects and sys.schemas are system views.

August 14, 2009 9:05 PM

mscheuner said:

Why does everyone still use "sys.objects" and "t.type = 'U'" ?? Wouldn't it be a lot easier to just use "sys.tables" in the first place?

August 15, 2009 2:27 AM

John Paul Cook said:

Because the documentation for sys.tables says "currently only with sys.objects.type = U", which means it could change. So, using type = U is still needed with sys.tables.

August 15, 2009 5:14 AM

aprato said:

Mea culpa.  I knew they were views but I called them tables.  Sorry about that.

August 15, 2009 4:31 PM

Leave a Comment


About John Paul Cook

John Paul Cook is a database and Azure specialist in Houston. He previously worked as a Data Platform Solution Architect in Microsoft's Houston office. Prior to joining Microsoft, he was a SQL Server MVP. He is experienced in SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. John is also a Registered Nurse currently studying to be a psychiatric nurse practitioner. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2. Connect on LinkedIn

This Blog



Privacy Statement