5

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.

Nick Kavadias
  • 10,796
  • 7
  • 37
  • 47
Martin
  • 572
  • 4
  • 14
  • 26

2 Answers2

6

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

Scott Whigham
  • 141
  • 3
  • 4
    I think it's well appropriate for SF from the dba perspective. – squillman Sep 03 '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 03 '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 03 '09 at 17:34
2

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)
Nick Kavadias
  • 10,796
  • 7
  • 37
  • 47
  • 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 04 '09 at 12:46