Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

3 Answers 3

up vote 7 down vote accepted

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.

share|improve this answer
    
Thanks to edit suggestion - changed Table to TableName. –  Mike Walsh Nov 18 '13 at 5:03
    
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 Nov 18 '13 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 Nov 18 '13 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 Nov 18 '13 at 5:17
    
Yes. There in 2005 - 2014 and beyond at this point :-) –  Mike Walsh Nov 18 '13 at 5:18
show 4 more comments

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!).

share|improve this answer
    
+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 Nov 18 '13 at 15:26
add comment

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.

share|improve this answer
2  
I think you could write a custom condition that checks this. +1 for a totally different way to do this. –  Jon Seigel Nov 20 '13 at 23:09
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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