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.

NUnit : Introduction

There are several unit test tools in the market, but one of them is NUnit, the most popular tool for doing unit testing. So in this tutorial...