List all heap tables of a given database

-- List all heap tables 
-- Try this with tempdb and see how many temp tables or table variables have been created there, you will be amazed with large olap databases.  
SELECT s.name + '.' + t.name AS TableName

FROM sys.tables t INNER JOIN sys.schemas s

ON t.schema_id = s.schema_id INNER JOIN sys.indexes i

ON t.object_id = i.object_id

AND i.type = 0 -- = Heap

ORDER BY TableName