Find tables not accessed for past n months in SQL Server database

Query below returns list of tables that was not accessed in the last 3 month.


In SQL Server you can find out when table was last accessed by quering dm_db_index_usage_stats view, but note that this view is cleaned each time SQL Server is restarted.


select [schema_name],
       max(last_access) as last_access
    select schema_name(schema_id) as schema_name,
           name as table_name,
           (select max(last_access) 
            from (values(last_user_seek),
                        (last_user_update)) as tmp(last_access))
                as last_access
from sys.dm_db_index_usage_stats sta
join sys.objects obj
     on obj.object_id = sta.object_id
     and obj.type = 'U'
     and sta.database_id = DB_ID()
) usage
where last_access < dateadd(month, -3, current_timestamp)
group by schema_name,
order by last_access desc;


  • schema_name - name of the database
  • table_name - name of the table
  • last_access - datetime of last access


  • One row represents one table in database
  • Scope of rows: all tables not accessed for past 3 months in database
  • Ordered by last access time descending

Sample results

sample results

There are no comments. Click here to write the first comment.