Identifying the largest HEAPS in your database

Running the below script will identify list off all HEAPs in your database (largest HEAPs will be listed first).

DECLARE @dbname as sysname

DECLARE @sql as nvarchar(1000)

DECLARE @heaps as table (dbname sysname, objectname sysname, create_dte smalldatetime, size_mb int)

DECLARE curs CURSOR FOR

SELECT name FROM master.sys.databases

WHERE state = 0 --online

and name <> 'TEMPDB'

OPEN curs

FETCH NEXT FROM curs INTO @dbname

WHILE @@fetch_status = 0

BEGIN

      SET @sql = 'USE ' + @dbname + char(10) + 'SELECT db_name() as dbname, o.name, o.create_date, (ps.in_row_used_page_count * 8096) / (1024 * 1024) as size_mb

      FROM sys.indexes i

      INNER JOIN sys.partitions p on p.object_id = i.object_id and p.index_id = i.index_id

      INNER JOIN sys.dm_db_partition_stats ps on ps.partition_id = p.partition_id

      INNER JOIN sys.objects o on o.object_id = i.object_id

      WHERE i.type = 0

      and o.type = ''U'''

      INSERT INTO @heaps (dbname, objectname, create_dte, size_mb)

      exec sp_executesql @sql

      FETCH NEXT FROM curs INTO @dbname

END

CLOSE curs

DEALLOCATE curs

SELECT *

FROM @HEAPS

order by size_mb desc