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,
    You have a typo: SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName.
    This is not DatabaseName.

    Reply
  • Such a good article to get last update of table, I found in web.Thanks

    Reply
  • Somehow not working for me ,

    Reply
  • i got 10k rows in my table, is it possible to find which row was modified or updated

    Reply
  • Felipe Queiroz
    June 14, 2018 10:01 pm

    In a third part server, I have no permissions to execute this statement. i receive this error:

    Msg 300, Level 14, State 1, Line 1
    VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’.
    Msg 297, Level 16, State 1, Line 1
    The user does not have permission to perform this action.

    Is there another way to get this information ?

    Reply
  • I’ve run into the same error and have the same question.

    Reply
  • Useful information, helped me at right situation.
    Thanks

    Reply
  • Hi,

    Is it possible to find which script user was used to modify a particular object

    Reply
  • Kevin Plunkett
    June 9, 2019 12:07 pm

    Hi,

    When looking for the Updated Date/Time on a table you have to take into account the Index and Partition as well. This is because SQL maintains separate metadata for each index part in each partition your table resides in… so you could update an index without updating the data.

    SELECT DISTINCT
    OBJECT_NAME([IUS].OBJECT_ID) AS [TableName],
    *
    FROM
    [sys].[dm_db_index_usage_stats] AS [IUS]
    INNER JOIN [sys].[partitions] AS [P]
    ON
    [IUS].object_id = [P].object_id
    WHERE
    [database_id] = DB_ID(‘{Database}’) AND
    [IUS].OBJECT_ID = OBJECT_ID(‘{schema}.{table}’) AND
    P.index_id = 1
    ;

    Reply
  • If I use the query together with OBJECT_ID and check for all databases, I don’t get any results. If use without “AND OBJECT_ID =” then I have some entries and object name is NULL – what does it mean? database was used or not?

    Reply
  • Paul Wichtendahl
    December 28, 2019 3:35 am

    It appears to only function correctly for me if I prefix the query with a ‘USE [table-name]’ statement. I am not certain of the NULL table names however the only results I get without both WHERE clause elements AND the USE statement are the index names.

    Reply
  • Luis Henrique Claudio Pereira
    August 11, 2020 10:57 pm

    303/5000
    Hello Pinal, how are you?
    I am your follower and starting in the SQL world, I need your help, I need a script that takes the last update command from a table, but you need to have the IP or name of the machine executed this update command, you can help me, I already thank you immensely.

    Reply
  • This only helps find changes since the last SQL restart!

    Reply

Leave a Reply

Menu