Find the time when table was last accessed in SQL Server database

Article for: IBM Db2 Teradata Vertica

Query below returns list of tables and their last using date.


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
group by schema_name, 
order by last_access desc;


  • schema_name - name of the schema
  • table_name - name of the table
  • LastAccess - last access time to table


  • One row represents one table in database
  • Scope of rows: all tables that was accessed in current SQL Server uptime
  • Ordered by last access time

Sample results

sample results

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