Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

SQL Server – How to get last access/update time for a table

Modify date and create date for a table can be retrieved from sys.tables catalog view. When any structural changes are made the modify date is updated. It can be queried as follows:

USE [SqlAndMe]

SELECT    [TableName] = name,
FROM    sys.tables
WHERE    name = 'TransactionHistoryArchive'



sys.tables only shows modify date for structural changes. If we need to check when was the tables last updated or accessed, we can use dynamic management view sys.dm_db_index_usage_stats. This DMV returns counts of different types of index operations and last time the operation was performed.

It can be used as follows:

USE [SqlAndMe]

SELECT    [TableName] = OBJECT_NAME(object_id),
last_user_update, last_user_seek, last_user_scan, last_user_lookup
FROM    sys.dm_db_index_usage_stats
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 important to note that sys.dm_db_index_usage_stats counters are reset when SQL Server service is restarted.


Hope This Helps!


If you like this post, do like my Facebook Page –> SqlAndMe
EMail me your questions ->
Follow me on Twitter -> @SqlAndMe

Filed under: Catalog Views, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012


Leave a comment on the original post [, opens in a new window]

Loading comments...