SQL Server 2016 Hosting – HostForLIFE.eu :: Getting The Last Access or Update Time For a Table on SQL Server

Peter Cole | November 8, 2017 | SQL 2014 Hosting | No Comments

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:

USE [SqlAndMe]
GO
 
SELECT    [TableName] = name,
create_date,
modify_date
FROM    sys.tables
WHERE    name = 'TransactionHistoryArchive'
GO

USE [SqlAndMe]

SELECT [TableName] = name,

create_date,

modify_date

FROM sys.tables

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:

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

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 vital to notice that sys.dm_db_index_usage_stats counters are reset when SQL Server service is restarted. Hope this article works!

Related Posts

About The Author

Pete