How to get a last access time for tables in SQL Server ?
To get the last time when table was accessed in SQL Server, you can use SQL Server dynamic management view sys.dm_db_index_usage_stats, which returns counts of different types of index operations and the time each type of operation was last performed.
DMV sys.dm_db_index_usage_stats counters are reset when SQL Server service is restarted.
Following statement, uses DMV sys.dm_db_index_usage_stats, which returns last acccessed times for all the tables in database SQLServerTutorials.
SELECT OBJECT_NAME(object_id) AS [Table] ,
Last_user_update, Last_user_seek, Last_user_scan, Last_user_lookup
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID('SQLServerTutorials')
GO
Following fields are returned by above statement :
Table is a table name belongs to a specifc database.
Last_user_update is a time of last user update.
Last_user_seek is a time of last user seek.
last_user_lookup is a time of last user lookup.
If you want to see the last access time for specific table then you can modify above statement as given below.
Following statement returns the last accessed time for table salesDetails.
SELECT OBJECT_NAME(object_id) AS [Table] ,
Last_user_update, Last_user_seek, Last_user_scan, Last_user_lookup
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID('SQLServerTutorials')
AND OBJECT_NAME(object_id) = 'SalesDetails'
GO
SQL Server Interview Questions
