Unused Heaps? - Quick Tips: Day 10

Don't forget! This coming Monday is the last Monday to win a free month of Pluralsight! All it takes is a single comment on the post that day and some way to get your E-Mail address to give you the code. The Monday following that, December 30th, will have a different prize. I will be giving away a free book and mailing it to your house. If you live out of the continental US, I will still send you a 25$ Amazon card to be used however you want. It's at least something to ring in the new year. ^.^

A question came up recently. How can you find heap tables that have not been used? This is the best answer I have so far. This will allow you to pull a list of all unused heap tables since last reboot. Keep that mind. This will only show you since last reboot or last time the dmv's were cleared for any reason.

--single database use
declare @db_id smallint
set @db_id = DB_ID(db_name())
select db.name as DatabaseName, sch.schema_id as SchemaName, tbl.name as TableName, idx.name as Indexname,
stats.index_type_desc as IndexType, stats.page_count as PageCount
 from sys.dm_db_index_physical_stats (@db_id, null,null,null,null) stats
left outer join sys.dm_db_index_usage_stats as usage on usage.object_id = stats.object_id
inner join sys.tables as tbl on tbl.object_id = stats.object_id
inner join sys.indexes as idx on idx.object_id = stats.object_id and idx.index_id = stats.index_id
inner join sys.schemas as sch on sch.schema_id = tbl.schema_id
inner join sys.databases as db on db.database_id = stats.database_id
where stats.index_id = 0
and usage.object_id is null
order by page_count desc

This will work for multiple databases. Be careful though. This will take some time to run on larger systems.

--multiple database use
exec sp_msforeachdb @command1 = '
use ?
begin
declare @db_id smallint
set @db_id = DB_ID(db_name())
select db.name as DatabaseName, sch.schema_id as SchemaName, tbl.name as TableName, idx.name as Indexname,
stats.index_type_desc as IndexType, stats.page_count as PageCount
 from sys.dm_db_index_physical_stats (@db_id, null,null,null,null) stats
left outer join sys.dm_db_index_usage_stats as usage on usage.object_id = stats.object_id
inner join sys.tables as tbl on tbl.object_id = stats.object_id
inner join sys.indexes as idx on idx.object_id = stats.object_id and idx.index_id = stats.index_id
inner join sys.schemas as sch on sch.schema_id = tbl.schema_id
inner join sys.databases as db on db.database_id = stats.database_id
where stats.index_id = 0
and usage.object_id is not null
end'

Alternately, you can also see what heap tables you have that are in use and how big they are.

--single database use
declare @db_id smallint
set @db_id = DB_ID(db_name())
select distinct db.name as DatabaseName, sch.schema_id as SchemaName, tbl.name as TableName, idx.name as Indexname,
stats.index_type_desc as IndexType, stats.page_count as PageCount
 from sys.dm_db_index_physical_stats (@db_id, null,null,null,null) stats
left outer join sys.dm_db_index_usage_stats as usage on usage.object_id = stats.object_id
inner join sys.tables as tbl on tbl.object_id = stats.object_id
inner join sys.indexes as idx on idx.object_id = stats.object_id and idx.index_id = stats.index_id
inner join sys.schemas as sch on sch.schema_id = tbl.schema_id
inner join sys.databases as db on db.database_id = stats.database_id
where stats.index_id = 0
and usage.object_id is not null
order by page_count desc