SQL SERVER – Find Last Date Time Updated for Any Table

I just received an email from one of my regular readers who is curious to know if there is any way to find out when a table is recently updated (or last date time updated). I was ready with my answer! I promptly suggested him that if a table contains UpdatedDate or ModifiedDate date column with default together with value GETDATE(), he should make use of it. On close observation, the table is not required to keep history when any row is inserted. However, the sole prerequisite is to be aware of when any table has been updated. That’s it!

If a user wants to find out when was the last table updated he can query dynamic management view (DMV) – sys.dm_db_index_usage_stats and easily figure out when was the table updated last. Let us comprehend this example by creating a table and updating it. We can use DMV to determine when it was updated last.

USE AdventureWorks
GO
CREATE TABLE Test
(ID INT,
COL VARCHAR(100))
GO
INSERT INTO Test
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
GO

Now we have created a table and populated it with data. Next, we will run the following query to find out when it was last updated.

SELECT OBJECT_NAME(OBJECT_ID) AS TableName,
 last_user_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'AdventureWorks')
AND OBJECT_ID=OBJECT_ID('test')

Running query provides accurate details of when was the table last updated. If WHERE condition is entirely removed it will provide details of the entire database.

SQL SERVER - Find Last Date Time Updated for Any Table lastupdated-800x441

Reference : Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQLAuthority News – Future of Business Intelligence and Databases – Article by Nupur Dave
Next Post
SQL SERVER – 10 Reasons for Database Outsourcing

Related Posts

187 Comments. Leave new

  • Hi Pinal,
    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 above date? Can I 100% believe on results return by sys.dm_db_index_usage_stats for Last)user_update?

    Please clarify.

    Reply
  • Hi Pinal,

    This shows last user update, do you know of any way to find out when the last table update was made in general, like through an application? A lot of the tables in my environment get updated through applications, and we are in the process of doing an inventory on old junk tables/records. I need to know the relevance of new updates/inserts in these tables in order to deem them junk or still relevant. Any ideas?

    Thank you

    Reply
  • Hi Pinal, In one of my table last_user_update is null. what should I believe in this case ? can you please suggest.

    Reply
  • Jineesh Uvantavida
    July 1, 2016 11:38 am

    how can i find out all changes made to my specific table. I would like to get the hostname (like the stored procedure change output) who changed my table in sql server. Some one deleted all my entries in a table. That is why i needed this. I think you would help me.

    Reply
  • virag sakhida
    July 4, 2016 11:53 am

    how to know that last updated record from multiple datatabase for today perticular day

    Reply
  • Can I find How many rows are inserted in certain table today without having any date column in table?

    Reply
  • Hi Pinal,

    I have to fire a trigger when a record got modified in above dmv., and store the data in another table. How can this possible. It is possible

    Reply
  • Hi Pinal,

    I do the columns on “user updates” . May i know number stated is it refer as frequency? What is the range period of this frequency?
    Thank you.

    Reply
  • I do see* the columns on “user updates” . May i know number stated is it refer as frequency? What is the range period of this frequency?
    Thank you.

    Reply
  • SELECT
    DB_NAME(indexStats.database_id) as databaseName
    , OBJECT_NAME(indexStats.”object_id”, indexStats.database_id) as objectName
    , sum(indexStats.user_seeks ) as user_seeks
    , sum(indexStats.user_scans ) as user_scans
    , sum(indexStats.user_lookups ) as user_lookups
    , sum(indexStats.user_updates ) as user_updates
    , max(indexStats.last_user_seek ) as last_user_seek
    , max(indexStats.last_user_scan ) as last_user_scan
    , max(indexStats.last_user_lookup) as last_user_lookup
    , max(indexStats.last_user_update) as last_user_update
    , sum(indexStats.system_seeks ) as system_seeks
    , sum(indexStats.system_scans ) as system_scans
    , sum(indexStats.system_lookups ) as system_lookups
    , sum(indexStats.system_updates ) as system_updates
    , max(indexStats.last_system_seek ) as last_system_seek
    , max(indexStats.last_system_scan ) as last_system_scan
    , max(indexStats.last_system_lookup) as last_system_lookup
    , max(indexStats.last_system_update) as last_system_update
    FROM sys.dm_db_index_usage_stats as indexStats
    group by
    indexStats.database_id
    , indexStats.”object_id”
    order by
    indexStats.database_id
    , indexStats.”object_id”
    ;

    Reply
  • In my Server I have around 15 databases. For some the records returned by this query I am unable to get the table name. Can any one please help

    Reply
  • OBJECT_NAME(OBJECT_ID) AS DatabaseName is not correct.
    OBJECT_NAME(OBJECT_ID) returns the object name which in your example is the table name.

    Reply
  • Dear Sir,

    I am developing a web application , and need to write a sql query demo satisfy 3nf any change in master table should reflect in all other table where ever that common id shares integrated with PHP and mysql. It would be really helpful if you give me some example that explains clearly.

    Reply
  • Anders Lindén
    March 7, 2017 3:11 pm

    I get multiple lines from SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
    FROM sys.dm_db_index_usage_stats
    WHERE database_id = DB_ID( ‘dbname’)
    AND OBJECT_ID=OBJECT_ID(‘tablename’)

    each with different index_id:s, none of them being 0, however.

    Reply
    • I believe you need to look at the one for the clustered index (id=1). Logically, that should have highest number.

      Reply
    • Corrected Code

      select object_name(object_id, db_id(‘your database name’)) as TableName, last_user_update
      from sys.dm_db_index_usage_stats
      where database_id = DB_ID( ‘your database name’) and object_id=(select object_id from your database name.sys.objects where name like ‘your table name’)

      Reply
  • This will give last data on which table accessed by uses.

    select DISTINCT DB_NAME(database_id)AS ‘DATABASENAME’,object_name(object_id) AS ‘OBJECT_NAME’ ,
    MAX(COALESCE(last_user_lookup ,
    last_user_scan ,
    last_user_seek ,
    last_user_update )) [Last time Table Accessed]
    from sys.dm_db_index_usage_stats
    where database_id =10 —- CHANGE DB_ID NUMBER HERE
    and (last_user_lookup IS NOT NULL
    OR last_user_scan IS NOT NULL
    OR last_user_seek IS NOT NULL
    OR last_user_update IS NOT NULL)
    GROUP BY DB_NAME(database_id) , object_name(object_id)

    Reply
  • Hi Pinal,

    when run the above script i get the table access dates as 2 or 3 days before. What about other tables which are accessed one month back or one year back.

    Reply
  • When service gets restarted then we will not be able to find the active and inactive tables with this query, how can we handle this?

    Reply
  • Frederic Malenfant
    July 27, 2017 11:53 pm

    Thank you, that help me generate a query to find which databases to backup every night!

    DECLARE @lastBackupDate DATETIME2
    SET @lastBackupDate = GETUTCDATE() – (25.0 / 24.0)

    SELECT DISTINCT
    ‘backup database [‘ + abc.dbname + ‘] … ‘ AS bkpQuery,
    abc.dbname
    FROM (
    SELECT
    (SELECT MAX(v) FROM (VALUES (last_user_seek), (last_user_scan), (last_user_update)) AS VALUE(v)) AS maxDate,
    d.name as dbname
    FROM sys.dm_db_index_usage_stats AS ddius
    inner JOIN sys.databases AS d
    ON d.database_id = ddius.database_id AND d.database_id > 4) abc
    WHERE
    abc.maxdate > @lastBackupDate
    ORDER BY
    abc.dbname

    Reply
  • Hi Pinal ,

    Please let me know how we can find the last ModifiedOn datetime of a column in SQL SERVER 2008

    Reply
  • Malenkii monster
    February 7, 2018 1:49 am

    this is so awesome, just what i needed

    Reply

Leave a Reply

Menu