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] GO SELECT [TableName] = name, create_date, modify_date FROM sys.tables WHERE name = 'TransactionHistoryArchive' GO
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] GO 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' GO
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!
Filed under: Catalog Views, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012