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.

3 Answers 3


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.


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.

  • well the object_id will work I thought we are using the function.but the sys.tables itself gives the id and thanks for showing this wonderful function.
    – Biju jose
    Commented Nov 18, 2013 at 5:05
  • No problem. It is a good function to have. Lot of properties. In this case you have it right - sys.tables already lists the object_id inside of it. and that is the object_id we want to pass in for the ID parameter to the OBJECTPROPERTY function. Thanks for the good catch on the reserved keyword I used there :)
    – Mike Walsh
    Commented Nov 18, 2013 at 5:11
  • ,you were precisely correct about the object_id function, I just mixed things up there.well thanks for pointing it out.well the objectproperty() is available from 2005 onwards,I just checked bol,right?
    – Biju jose
    Commented Nov 18, 2013 at 5:17
  • Yes. There in 2005 - 2014 and beyond at this point :-)
    – Mike Walsh
    Commented Nov 18, 2013 at 5:18
  • I edited the script to add Schema Name. Note that (like OBJECTPROPERTY) the OBJECT_SCHEMA_NAME() function was new to MSSQL 2005. Commented Nov 18, 2013 at 19:45

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.

    OBJECT_SCHEMA_NAME(t.object_id) AS SchemaName,
    t.name AS TableName
    FROM sys.tables t
        NOT EXISTS
            SELECT *
                FROM sys.indexes i
                    (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!).

  • +1 for "because as you said, going through each table manually is impractical (and prone to error!)." Amen there. Very prone to error :)
    – Mike Walsh
    Commented Nov 18, 2013 at 15:26

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.

  • 2
    I think you could write a custom condition that checks this. +1 for a totally different way to do this.
    – Jon Seigel
    Commented Nov 20, 2013 at 23:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.