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



Using sys.table

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


USE [Database Name]
GO

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


sys.table is shown 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.

Using sys. sys.dm_db_index_usage_stats 

sys. sys.dm_db_index_usage_stats , This DMV returns counts of different types of index operations and the last time the operation was performed.
See the following query.


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


Note: last_user_update – provides time of last user update

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

No comments:

Post a Comment

Please do not enter any spam link in the comment box.

Related Posts

What is the Use of isNaN Function in JavaScript? A Comprehensive Explanation for Effective Input Validation

In the world of JavaScript, input validation is a critical aspect of ensuring that user-provided data is processed correctly. One indispensa...