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

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.