heap of wood

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

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

Share

Share

Rate