6

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.

2 Answers 2

7

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

3
  • 4
    I think it's well appropriate for SF from the dba perspective.
    – squillman
    Sep 3, 2009 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! Sep 3, 2009 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, 2009 at 17:34
3

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)
1
  • 1
    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, 2009 at 12:46

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .