Get the all heap table from a database in sql server

A Table that doesn’t have a Clustered Index is referred to as a HEAP Table. With the help of below sql script we will get the list of heap tables in a database.

SELECT T.Name 'HEAP TABLE', I.type_desc FROM sys.indexes I          INNER JOIN sys.tables T         ON I.object_id = T.object_id WHERE I.type = 0 AND T.type = 'U'

See the output