Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Is it possible to produce a list of all tables within a specified database that don't have a primary key?

This is SQL Server 2005.

share|improve this question
up vote 5 down vote accepted

This really belongs more on StackOverflow rather than ServerFault, doesn't it?

Anyway, I found this script that I posted a few years back: List all tables without a primary key. It works for SQL 2000+.

share|improve this answer
I think it's well appropriate for SF from the dba perspective. – squillman Sep 3 '09 at 16:28
I'm new here and still trying to figure it all out :) I thought SO was for development and SF was for admin and I guess I got confused! I think this is definitely a DBA-level script so I think you are right. Sorry about that! – Scott Whigham Sep 3 '09 at 17:07
For what it's worth I did deliberate about where to put it. My conclusion being that the required reason for wanting to know is so I can set up replication - nothing to do with programming. – Martin Sep 3 '09 at 17:34

yikes! information schema views are ugly.

If your database is 2005, then you should use the catalog views, as recommended by MS

Here's my solution:

* from sys.tables
WHERE object_id not in
(SELECT object_id from sys.indexes WHERE is_primary_key=1)
share|improve this answer
Only a one off job, so not too bothered, however have voted you up for other uses who may have this enquiry. – Martin Sep 4 '09 at 12:46

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.