Today, I wanna talk about Getting The Last Access or Update Time For a Table on SQL Server 2016. Modify date and build date for a table are often retrieved from sys.tables catalog view. once any structural changes are created the modify date is updated. It can be queried as follows:
SELECT [TableName] = name,
WHERE name = 'TransactionHistoryArchive'
The sys.tables solely shows modify date for structural changes. If we’d like to check when was the tables last updated or accessed, we are able to use dynamic management view sys.dm_db_index_usage_stats. This DMV returns counts of various varieties of index operations and last time the operation was performed. It are often used as follows:
SELECT [TableName] = OBJECT_NAME(object_id),
last_user_update, last_user_seek, last_user_scan, last_user_lookup
WHERE database_id = DB_ID('SqlAndMe')
AND OBJECT_NAME(object_id) = 'TransactionHistoryArchive'
last_user_update – provides time of last user update
last_user_* – provides time of last scan/seek/lookup
It is vital to notice that sys.dm_db_index_usage_stats counters are reset when SQL Server service is restarted. Hope this article works!