How do I find all tables in a DB that have no explicit primary key?

Question

A Google search spewed forth millions of hits on how to find tables without clustered indexed, the PK normally being the clustered index of a table. However, a table could easily have a natural key as a clustered index, and a non-clustered surrogate index, like an identity column.

How do I find all tables in a DB without a primary key defined? I have 245 tables in this DB: manual inspection is grossly inefficient.

Answer 1

Couple ways to skin this cat but this works fine in SQL Server 2005 and up, and I find it a pain free way to handle the problem -

The OBJECTPROPERTY() function can list various properties about objects - like tables. One of those properties is whether or not a table has a primary key.

OBJECTPROPERTY(object_id, tablehasprimarykey) = 0 would be a table without a primary key.

So

SELECT OBJECT_SCHEMA_NAME( object_id ) as SchemaName, name AS TableName
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'tablehasprimaryKey') = 0 
ORDER BY SchemaName, TableName ;

Should give you what you need. You can see all about the other ways to use the OBJECTPROPERTY() function in books online. This is the 2012 version of the article.

Answer 2

Mike's solution is excellent for the specific problem.

If you want more flexibility, here's an alternative that can be easily morphed into a query that returns other information, such as finding all tables that are heaps, or finding tables that have no unique constraints at all.

SELECT
    OBJECT_SCHEMA_NAME(t.object_id) AS SchemaName,
    t.name AS TableName
    FROM sys.tables t
    WHERE
        NOT EXISTS
        (
            SELECT *
                FROM sys.indexes i
                WHERE
                    (i.object_id = t.object_id) AND
                    (i.is_primary_key = 1)
        );

Once your (sub)system goes over ~50 tables, it's really important to get familiar with all the metadata tables, because as you said, going through each table manually is impractical (and prone to error!).

Answer 3

The Policy Management feature of SQL Server can do some of this.

The Table facet has fields @HasIndex and @HasClusteredIndex (as well as other ones that may be useful, like triggers). A policy can be created to check conditions on all tables, in all databases, in a number of servers (using the Central Management Server feature).

It can't, however, check the existence of a primary key index or constraint. I would have sworn that there was a field @HasPrimaryKey but it's not there in MSSQL2012. I'm either misremembering or going mad.

Note: Policy Management is included with SQL Server 2012 Enterprise, Business Intelligence and Standard editions. It is not available in Express edition.