Index Basics: Find Tables That Do Not Have A Clustered Index

In a previous Index Basics post we went over the importance of having a Clustered Index on your tables, and we learned that SQL Server will automatically create a Clustered Index on the Primary Key column(s) of your table, unless you explicitly tell it not to.

But what about tables that don’t have Primary Keys?  Is there a way to get a list of them and tell if they do or do not have a Clustered Index?  Yes there is.  SQL Server makes this information easily available via the sys.indexes DMV.  In the below query, I also join on the sys.objects and sys.schemas DMVs to get the table and schema names.

This query will return a list of tables that do not have a Clustered Index.  It is database specific, so it only returns results from the database that you are running it against.

 

(Visited 284 times, 1 visits today)

2 thoughts on “Index Basics: Find Tables That Do Not Have A Clustered Index

  1. Jeff Moden Reply

    Nice, short, to-the-point article. Thanks for taking the time to post it.

    To extend the lesson and simplify the code, we can make use of some pretty cool built in functions. We don’t need to join to sys.objects or sys.schemas to come up with the object name or the schema.

    SELECT TableName = OBJECT_SCHEMA_NAME(object_id)
    + ‘.’
    + OBJECT_NAME(object_id)
    FROM sys.indexes
    WHERE index_id = 0 –Only on HEAPS (Tables with no clustered Index)
    AND OBJECTPROPERTY(object_id,’IsUserTable ‘) = 1
    ORDER BY TableName
    ;

  2. Eric Cobb Post authorReply

    Thanks Jeff!

    Yeah, I considered the built in functions, but I tend to try to avoid using any functions in my queries whenever possible. Especially when returning an unknown, possibly large, set of records. The developer in me cringes at the thought of forcing SQL into row-by-row operations by using functions.

    I’m sure the built in SQL Server functions are highly optimized, but my DBA-OCD just won’t let me do it! 😉

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.