Find All Heaps In Your Database

This is just going to be a quick post but I was asked the question the other day, how can I find all the tables in the database that don’t have a clustered index?

Tables without clustered indexes, known as heaps can give you quite a few different performance problems for various reasons. I don’t really want to go into those here, that could be a subject for another post but finding these heaps is actually very simple. Your friend is sys.indexes.

Have a look in that table and you can easily spot the heaps as those with a type of 0 or type_desc of HEAP.

You can use the following code to return all your heap tables.

SELECT OBJECT_NAME(object_id)
FROM sys.indexes
WHERE type = 0

3 thoughts on “Find All Heaps In Your Database

Add yours

  1. The query is returning multi-statement valued table functions in the results (not inline table functions for some reason).

    To exclude the table functions:

    SELECT OBJECT_NAME(object_id), *
    FROM sys.indexes
    WHERE type = 0
    and object_id not in (SELECT o.object_id
    FROM sys.sql_modules m
    INNER JOIN sys.objects o
    ON m.object_id=o.object_id
    WHERE type_desc = ‘SQL_TABLE_VALUED_FUNCTION’)

    Like

    1. Well I suppose it doesn’t return inline table valued functions since they don’t manifest anything, whereas a multistatement table function actually creates a table variable to return… but it’s still interesting.

      Like

      1. Good spot! I hadn’t thought about multi-statement table valued functions but they do show up in sys.indexes so it makes sense that the query would return them.

        Like

Leave a Reply to happydba Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: