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

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.