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

Question

SQL Server - Is there a system table for identifying if my table is heap or b-tree?

Answer 1

Yes, the catalog view sys.partitions holds this information. The field index_id will tell you if a table is heap (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 - Table and Index Organization:

ID of the index within the object to which this partition belongs.

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

Heaps are tables that have no clustered index. Nonclustered indexes have a B-tree index structure similar to the one in clustered indexes.