Oct 15, 2015

How to get last access/update date time of a table in SQL Server

Using sys.table catalog view we can access modified and created date. if any structural changes are made the modified date is updated. Following query may help you.

USE [Database Name]

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

sys.table is show only 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. sys.dm_db_index_usage_stats , This DMV returns counts of different types of index operations and last time the operation was performed.
See following query.

USE [codefari]
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('codefari')-- DBName
AND        OBJECT_NAME(object_id) = 'MyBlogs'--Table Name

Note: last_user_update – provides time of last user update

last_user_* – provides time of last scan/seek/lookup