A table without cluster index is called Heap table. Now you are thinking why we are talking about this.
So as SQL developer we should aware and In next post I will explain which is related to maintenance how to Rebuild a heap table.
Now our next question is how to determine all the heap table from a database
so below is simplest query to determine all the heap tables from a database
SELECT T.Name ‘Heaptable’
FROM sys.indexes I
INNER JOIN sys.tables T
ON I.object_id = T.object_id
WHERE I.type = 0 AND T.type = ‘U’
I ran this query on Adventureworks database and 2 tables with heap as shown in below figure
now in next post we will discuss how to rebuild fragmented heap table which is a maintenance activity.
I hope this article may help you.