Finding unused tables in SQL Server

Monday, 1 June 2009

Here's a query which can give you a clear idea about the unused tables in your database.

select 

t.name as 'table', 

sum(i.user_seeks + i.user_scans + i.user_lookups)  as 'total accesses',

sum(i.user_seeks) as 'seeks',

sum(i.user_scans) as 'scans',

sum(i.user_lookups) as 'lookups'

from 

sys.dm_db_index_usage_stats i right outer join 

sys.tables t on (t.object_id = i.object_id)

group by 

i.object_id, 

t.name

order by [total accesses] desc

This works in SQL server 2005/later