Home > Catalog Views, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012 > SQL Server – How to get last access/update time for a table

SQL Server – How to get last access/update time for a table

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!

Vishal

If you like this post, do like my Facebook Page –> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Advertisements
  1. February 12, 2015 at 6:58 pm

    Thanks for sharing this information. I was looking for this to form part of my monthly audits.

  2. Aviad
    May 18, 2015 at 8:03 pm

    Hi Vishal, thanks for the helpful post!
    How can you tell which user updated the table in addition to the update time?

    Thanks,
    Aviad

  3. Munesh Kumar
    August 6, 2015 at 4:12 pm

    Hi,
    Since my server was started one month back, I am getting last_user update (2015-07-31) for a table. That mean my table’s data is not being modified after (2015-07-31) date? Can I 100% believe on results return by sys.dm_db_index_usage_stats for Last_user_update?

    Thanks,
    Munesh Kumar

  4. Harsh
    August 15, 2015 at 5:20 pm

    Thanks. Its a very helpful information.

  5. Rodolfo
    October 28, 2015 at 1:31 am

    Thanks for me works without filter
    SELECT [TableName] = name,
    create_date,
    modify_date
    FROM sys.tables

  6. Sujana
    April 13, 2016 at 1:47 pm

    Hi..How to check this information in SQL 2000 version.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: