Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

2 Answers 2

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

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)
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

 
discard

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.