Three options to find heaps in MSSQL

finding-heaps.sql

-- Heap tables (Method 1) 
SELECT @@Servername AS ServerName ,
    DB_NAME() AS DBName ,
    t.Name AS HeapTable ,
    t.Create_Date
FROM sys.tables t
    INNER JOIN sys.indexes i ON t.object_id = i.object_id
        AND i.type_desc = 'HEAP'
ORDER BY t.Name
--OR 
-- Heap tables (Method 2) 
SELECT @@Servername AS ServerName ,
    DB_NAME() AS DBName ,
    t.Name AS HeapTable ,
    t.Create_Date
FROM sys.tables t
WHERE   OBJECTPROPERTY(OBJECT_ID, 'TableHasClustIndex') = 0
ORDER BY t.Name;
--OR 
-- Heap tables (Method 3) also provides row counts 
SELECT @@ServerName AS Server ,
    DB_NAME() AS DBName ,
    OBJECT_SCHEMA_NAME(ddps.object_id) AS SchemaName ,
    OBJECT_NAME(ddps.object_id) AS TableName ,
    i.Type_Desc ,
    SUM(ddps.row_count) AS Rows
FROM sys.dm_db_partition_stats AS ddps
    JOIN sys.indexes i ON i.object_id = ddps.object_id
        AND i.index_id = ddps.index_id
WHERE   i.type_desc = 'HEAP'
    AND OBJECT_SCHEMA_NAME(ddps.object_id) <> 'sys'
GROUP BY ddps.object_id ,
	        i.type_desc
ORDER BY TableName;