Home / server / Questions / 62046
Accepted
Martin
Asked: 2009-09-04 08:19:12 +0800 CST

SQL Server - list all tables without a primary key

  • 772

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.

sql-server

2 Answers

  1. Best Answer

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

    • 6

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