SQL Server - how to determine if a table is a heap or a B-tree

Question

SQL Server - is there a system table to determine if my table is a heap or a b-tree?

Answer 1

Yes, the sys.partitions directory contains this information. The index_id field will tell you if the table is a bunch (index_id = 0) or b-tree (index_id> 0).

 select o.name, o.object_id, case when p.index_id = 0 then 'Heap' when p.index_id = 1 then 'Clustered Index/b-tree' when p.index_id > 1 then 'Non-clustered Index/b-tree' end as 'Type' from sys.objects o inner join sys.partitions p on p.object_id = o.object_id where name = 'YourTableName' 

From the documentation - Organization of tables and indexes :

The index identifier within the object to which this section belongs.

 0 = heap 1 = clustered index 2 or greater = nonclustered 

Heaps are tables that do not have a clustered index. Non-clustered indexes have a B-tree index structure similar to that in cluster indexes.

Answer 2

Each table that has a clustered index is a heap table. you can check the clustered index for each table to determine if the table is a heap table or not.