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
- The statistics are cumulative since the last restart of the instance. A
NULLvalue means there was no access of that type since then. - Uncomment the
AND ius.[object_id] = ...line and replaceYourTableNameto limit the result to one table.