THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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 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.

 

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

Comments

 

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

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is a Technology Solutions Professional for Microsoft's data platform and works out of Microsoft's Houston office. Prior to joining Microsoft, he was a Microsoft SQL Server MVP. He is experienced in Microsoft 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 who graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. He volunteers as a nurse at safety net clinics. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2. Opinions expressed in John's blog are strictly his own and do not represent Microsoft in any way.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement