LLI Wiki — My Digital Knowledge Base

SQL Server: last seek, scan, lookup and update per table

List, for each table of the current database, the last access by lookup, scan, seek or update.

This query returns, for each table of the current database, the date of the last read (lookup, scan, seek) and of the last update, based on the index usage statistics (sys.dm_db_index_usage_stats). It aggregates the indexes of each table with MAX.

Query

SELECT DB_NAME(ius.[database_id]) AS [Database],

OBJECT_NAME(ius.[object_id]) AS [TableName],

MAX(ius.[last_user_lookup]) AS [last_user_lookup],

MAX(ius.[last_user_scan]) AS [last_user_scan],

MAX(ius.[last_user_seek]) AS [last_user_seek],

MAX(ius.[last_user_update]) AS [last_user_update]

FROM sys.dm_db_index_usage_stats AS ius

WHERE ius.[database_id] = DB_ID()

--AND ius.[object_id] = OBJECT_ID('YourTableName')

GROUP BY ius.[database_id], ius.[object_id]

Notes

Mastodon LLIMastodon SocialFosstodon