up vote 4 down vote favorite
share [g+] share [fb]

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.

link|improve this question
62% accept rate
feedback

2 Answers

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+.

link|improve this answer
4  
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
1  
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
feedback
up vote 1 down vote

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:

SELECT
* from sys.tables
WHERE object_id not in
(SELECT object_id from sys.indexes WHERE is_primary_key=1)
link|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
feedback

Your Answer

 
or
required, but never shown
discard

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