Nothing exciting, just a general script. How can you see what heaps you have laying around?
exec sp_MSforeachdb @command1 ='
AS TableName, i.object_id,
AS SchemaName, i.name, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL) p
inner join sys.objects as o on p.object_id = o.object_id
inner join sys.schemas as s on o.schema_id = s.schema_id
inner join sys.indexes as i on p.object_id = i.object_id
and p.index_id = i.index_id
where i.index_id = 0
and page_count > 1000
Adjust the page count to what ever works for your area. This is just for exploratory uses.