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. 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 finds 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
SELECT 1,'First'
SELECT 2,'Second'

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 DatabaseName, last_user_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'AdventureWorks')

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.

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

About these ads

139 thoughts on “SQL SERVER – Find Last Date Time Updated for Any Table

  1. @ Pinal Dave.

    Many times I have seen people asking similar question, when was my database/ table last accessed (Tables selected/ inserted / update) . So they can decide if they can drop or take this database offline or drop unwanted tables.

    Below script also gives information about those table that were used in select statements, giving information of when a table was last accessed ( selected/Inserted / Updated ).

    This is script :

    sys.dm_db_index_usage_stats i JOIN
    sys.tables t ON (t.object_id = i.object_id)
    database_id = db_id()

    Note: One very important point to notice is, this information is coming from DMV. If you restart SQL Server, this information is reset. Meaning if you restart sql server and execute above script you will not see any information, because every time sql server restarts, all this information coming from DMV will be reset.

    ~ IM.

  2. Cool idea.

    The only gotcha is when you run across a table (or set of tables) that has no indexes. of any sort, not even a primary key, let alone anything useful.

    Because the DBA before you didn’t think they’d be necessary because “SQL Server does all that for you. It’s set and forget”

    Not that I am speaking from experience or anything.

  3. @Dave,

    Even if you do not have any indexes on your table, you can still execute above script, this should work fine. It will give you correct results.

    DMV used is sys.dm_db_index_usage_stats, it is slightly misleading because it does provides details for all table, tables with indexes and with out indexes.

    ~ IM.

  4. On my machine, the second query only works if “Test” is written with a capital T .


    SELECT query : (…) AND OBJECT_ID=OBJECT_ID(‘test’)

    So, the query is case sensitive…

  5. Very nice Pinal.

    Is there a page on your website that lists all the scripts you have (with a link back to the original article)? That would eb a great page to bookmark.

  6. Pinal,

    That is a good start. It would still need to be categorized.

    If i have the free time i may just dig in to it.

  7. Hi Pinal,

    We can also see the modifydate in sys.tables view.

    select * from sys.tables where type=’u’ and name=’Table_Name’

    is this fine to see the modified date for a particular table.
    and can you please tell me to see the modified column name and the modified date in a table. because i couldnt see the modify date in sys.columns view for a specific column.

    Thanks in advance

    • How does it work in SQL2000 as Bis has asked?
      i need to take offline SQL2000 databases and i am not sure if they are still in production.
      I have option to run SQL Trace but i am trying to avoid this since I have 50 + servers for this audit.

      Thanks in Adv.

  8. Hi Pinal,
    Please help me to find the solution to get the list of table of database where records has inserted, deleted or modified in MS Sql Server 2005.

  9. @Yousuf,

    As far as my knowledge for SQL Server goes, SQL Server does not store which user updated or added a record in table.

    If you want to do that, you need to add a column Created_By and Last_Updated_By, and put the default value as suser_sname().

    When ever a user updates this tables, this values will be recorded in Last_Updated_by column.

    You can use SQL Profiler or traces, but these tools give information that is current… They do not give historical information, unless you have set traces that collect auditing information.

    ~ IM.

  10. Insert the datevalues into the table.

    create table dbo.my (datevalue datetime)

    declare @time as datetime

    set @time=’9/9/1995′

    while @time<'9/9/2020'
    insert dbo.my values (@time)

    set @time=@time+1


  11. well very nice stuff….

    is there is any way i will find out in SQLServer that the table which is updated in database then on this updated Table on which One or all the Row are effected..

    kindly please explain it if we find this info by using this dmv…

  12. Hi Noman,

    There is no inbuilt functionality to know which row was changed and what was the changes. But you can design your method to track such details by using triggers, additional timestamp, checksum or datimtime column to check and track changes in a row.
    You can also configure traces (profiler) to log all or perticuler changes. As we know, all the changes are completely and sequentially recorded in transaction log but SQL Server does not have any tool to view it. But you can view it using third party tools.

    Pinal Dave

  13. Hi, I have a database with a table equipment. whose primary key is a datetime that receives contents every 10 seconds for several days.
    I need to perform a selection of all data between 2010-01-15 14:02:42.125 and 2010-01-16 14:03:12.126. How do I make this query without separating the days from the hours?
    if any of you could give me a hint I would be very grateful.

  14. @ Pinal

    Hey, This is pretty useful. However for one of the databases that I have, for all the tables in it, the last_user_update is NULL. Why would that be, does it mean that the data hasn’t been captured or does that mean that after the last update, maybe the server was re-started and that info was lost?

    Also for a complete newbie, what does the last_user_seek and last_user_scan dates mean and how is it different that the last_user_update?


  15. Hello Divya,

    Is the SQL Server restarted recently? Because every time server restarted the count and date statistics is removed from database.
    Te columns last_user_seek, last_user_scan dates and last_user_update are self explanatory. These are last date when index or table was seek, scan or updated.

    Pinal Dave

  16. Thanks, Pinal. Thats helpful. I am guessing the server was restarted a while back. Now that its all null, is there any other way to know if this database hasnt been used in a while in which case it can be safely retired ?

    As for the other databases that did have values for last_user_update, I noticed that for some tables of the database, the column had the time when I actually ran the above query, why would that happen?


  17. Cant get this to work with SQL Server Express :-(

    Do you know if Express is able to return data/time of last table update ?

    Im trying to maintain a set of tables on a CE device that are mirrors of those on SQL Server Express desktop machine. Updates only occur on the Server, CE doesnt change the tables at all.

    I’d rather not use direct SQL over the network because i dont think i can be sure they will be atomic in the event of a network outage.


  18. It is telling “Invalid object name ‘sys.dm_db_index_usage_stats’.” when executing the below stmt….can any one plz help on this

    SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
    FROM sys.dm_db_index_usage_stats
    WHERE database_id = DB_ID( ‘AdventureWorks’)

  19. Hi Pinal – Is there a way to get the most recently inserted record in a table?

    Ex:I need to email users if a new employee is added. My company doesnt allow me to create trigger in the tables. My only other idea is to create a dummy table and compare that with original table daily. Is there any other better approach to this?

    • This is how I would do it.

      Add BIT column to the employee table which defaults to 0 and which tells if employee is “old” or not. Then update employee table and set this value to 1 so you don’t get a heck load of emails flying around on next scan ;)

      Then write a procedure to scan the table for new employees:

      CREATE PROCEDURE [dbo].[GetNewEmployees]

      IF @@TRANCOUNT = 0 RAISERROR(‘Must be called within transaction.’, 16, 1)

      SELECT * INTO #emp FROM employess WHERE IsOld = 0
      UPDATE employess SET IsOld = 1 WHERE IsOld = 0
      SELECT * FROM #emp


  20. Hello Kathis,

    In SQL Server 2008 a new feature is introduced called CDC (change data capture) to track changes.
    In older version you can check value of any datetime columns if you are storing the date-time of record insertion.
    Or you can use any new-record flag column to mark a record as new.

    Pinal Dave

    • Thanks for your reply Pinal. In my case its an ERP system DB where i cant touch the existing tables. I think i need to go with the approach of creating a dummy table and check that with original table daily.

      Thanks again

  21. Pinal..

    Nice post and a valuable info.
    I tried it in SQL2005 and it was working.

    But please let me know ( if any), the ways which I can get the details even after the SQL Server is restarted.

    Thanks and Regards


  22. hi pinal,

    i have restored the affected DB which one send by my client to me.but this script is not working in it.

    why this script is not working when i restore the DB? when i create the backup for any DB will it not taken the related system tables information?


    • I have no credate and update field in my table. how can i find the record insert date without these fields. please help for this issue.

  23. HI Pinal

    I am reading all these posts. Just trying to see if there is a simple way to know which tables in a SQL DB were updated if I run a process that updates the Database. I want to run one step in a piece of software and determine which tables were just updated, then keep repeating that process. I’m trying to understand a program better by looking at which tables just got updated when I ran a step in a piece of software that uses that SQL DB (SQL Server 2005)

    Thank you for any insights beyond your example. I tried your example script and most of the tables were Null.


  24. while executing below message is coming

    invalid object name
    can you please help about this

    SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
    FROM sys.dm_db_index_usage_stats
    WHERE database_id = DB_ID( ‘AdventureWorks’)

    Running query provides accurate details

    sharad patil

  25. Sharad, If you copy and paste the above query, it will have the smart quotes (enclosing ‘AdventureWorks’ and ‘test’). Replace them with normal single quotes.

    Charles Mutunga.

  26. Here is what I used:

    USE AdventureWorks
    select max(last_user_update) as LastModifiedDate from sys.tables s join
    sys.dm_db_index_usage_stats m on s.object_id = m.object_id where name = ‘TableName’

  27. Hi Pinal,

    What is the best process of migration of Database one sql server 2005 to other sql server 2005. in migration we require all login and password same, all users and password same in other sql server 2005. please let me step by step process.

    i am waiting ur answer.

    Jayesh G

  28. when i teied to run a simiilar query i got

    Msg 208, Level 16, State 1, Line 1
    Invalid object name ‘sys.dm_db_index_usage_stats’.

    How can I solve it ?

  29. Thank you for the article ‘SQL SERVER – Find Last Date Time Updated for Any Table’. I’m trying to find out the last time a linked server table was updated but without success. The query returns an empty row. Should you suggestion work?

  30. Is there a way to track the change/update log on a particular records in the table. We have few fields in some records and we have no clue how it got changed.

  31. hi pinal,
    I have a small issue. Is there a way to find out the last_updated_date for a particular column ?

    i need to populate a column changed_date in a table whenever a update or insert happens. It worked with using a trigger, but the requirement is to not use a trigger. Is there any system table which has the information everytime we update or insert a column and can we do it with contraints ?

    Need your take on this.


  32. Hi Pinal,

    Is there a way with which one can find out when was the last record-column was updated.

    Note: no column like last updated etc exists. This table has less than 500 records.

    Thanks in aticipation,

  33. Its wonderful to see all your inputs. I want to know if there is a way to know that when a particular record was updated or inserted, without using triggers etc. Is there any command to list the update/ insertion time of all/ selected records of a particular table. I am working with MSSQL 2000 and MSSQL 2008.


  34. hi pinal,

    How to get the 2 rows from each alphabet the table like this

    eno ename sal

    1 a 1000
    2 a 2000
    3 a 3000
    4 b 4000
    5 b 1000
    6 b 2000
    7 c 3000
    8 c 3000
    9 c 4000

  35. Nice script … few changes

    1) Minor Error: I think it is ObjectName (not database name)


    2) Also could you add @ the top the Imran Note:

    ” Note: One very important point to notice is, this information is coming from DMV. If you restart SQL Server, this information is reset. Meaning if you restart sql server and execute above script you will not see any information, because every time sql server restarts, all this information coming from DMV will be reset. ”


  36. Hi Pinal,

    If this works from 2005 versions, how is it done in 2000? I have a mixture or 2000, 2005 and 2008 servers that we need to clean up and I am looking for various methods in which this can be done. Appreciate your help


  37. The solution is good, thanks. But sometimes execution of the query returns more than 1 row. Rows has different value of index_id column. Could you please explain what is index_id column really means? MS explains it as “ID of the index”. So the question is how to interpret results in such case and where to find real update time in such case?

  38. Hi Pinal,
    This is a very useful article.
    We have an issue with one of our tables that are automatically updated and the developer is already gone. Is it possible to trace where the data is coming from? We are using SQL server 2000.
    Thanks in advance

  39. Sorry, the query will show the last update of INDEX, not of the table as the heading claims. It won’t capture an update, which does not update the index.

  40. Your query ‘SQL SERVER – Find Last Date Time Updated for Any Table’….how far does it go back.

    My last_user_update had NULLs all the way down the page on my test dev box.

    I know not to expect alot of data from a dev test box,
    but I was hoping it would have some date that might go back a year or two.

  41. Hi,

    With the above query we can find only the last ALTER TABLE deatils..

    I need to check for last one week details can u help me in this query….

  42. Hi sir,

    I have one problem , how to find current date record according to Srno Column like 1,2,3…

    srno DisplayName CreatedDate
    1 cvxbdfb currentdate

    like this..

  43. Hi Pinal,

    What is the best process of migration of Database one sql server 2005 to other sql server 2005. in migration we require all login and password are same, all users and password are same in other sql server 2005 installation. please let me step by step process.

    i am waiting ur answer.

    Jayesh G

  44. Someone from my team update passwords of my users table.
    How can i find that Update Query executed on which date and from where.

    Bad thing, i have already updated one row today.

  45. i want to discuss a prb related to sql server. I have read your articles and they really help me out.

    I have a table called as data table. I want to save a value(time stamp) in this table. now this is a single value i.e 7/25/2011 7:32:41 but it will be repeated equivalent to no of rows in table…..is there any method through which i can save this value in any stored procedure provided that stored procedure is associated with table too….

  46. This will work if you have not restared your PC right? My case is one database is on server and one is on my PC. PC i will start and shutdown everyday. Then i can not get correct info on my PC. Is there a way to get last updated date on my PC?

  47. select * from sys.dm_db_index_usage_stats

    the user does not have permission to perform this action.

    Is there any alternative to this ?

    • It means that you have very limited permissions on that server; most likely, a production server, on which you have read-only permissions for some tables. Yes, there is a possibility: encapsulate it in a stored procedure, and get permissions to run that stored procedure. I’m sure a SQL developer can help you.

  48. i know that there is no direct methods to check the table changes in SQL2000. But again, checking any possibilities .. the realtionship with a table in SQL 2000 is gone, there was around 15 FK relations and all of them are gone. and not sure when and why and by whom..
    Anyway to get that information?


  49. Hi Pinal,

    A table contains ModifedDate column, which need to be set with the server date when an update runs against that table. Can we do this with using Triggers? can we set any property for this column other than Default value property?

  50. I have a somewhat related interest: I want to find the date that an index was last altered (not when it was last accessed). For example, when analyzing a log of REORGANIZE and REBUILD activity, I will sometimes decide to alter the fillfactor of an index so it will require defragmentation less often. It would be handy to be able to find if and when that might have been done previously. (I wouldn’t expect to find that specifically, but the date of the most recent alter of an index regardless of what alteration was made would still be useful.)

  51. Very interesting, but in some tests here I got two rows back, one had NULL and the other a real-date the index numbers were 1 (real date value) and 16 (NULL date value).

    Is there a safe way to always get the most recent date if there are two or more rows returned for a table?

    How about:

    SELECT MAX(last_user_update) FROM sys.dm_db_index_usage_stats WHERE database_id = … ?

    This seems to work but I’m no SQL guru !


  52. Hi Pinal
    Nice useful post . But what about large amount of tables are getting updated every day?
    Do you have any script for that ?

  53. –To view all last updated tables of all databases,

    Execute sp_msforeachdb @command1=’DECLARE @PrintMessage NVARCHAR(50);SET @PrintMessage = “USE [?] Database Name”;USE [?];PRINT @PrintMessage;select top 5 Modify_Date as Last_Modified_Date,Create_Date,Name ,Type_Desc as Table_Name from sys.tables order by Modify_Date Desc';

    –To view all the databases at a time..for last update statistics
    Execute sp_msforeachdb @command1= ‘DECLARE @PrintMessage NVARCHAR(50),@PrintMessage1 NVARCHAR(50);SET @PrintMessage1 = “USE [?] Database Name”;USE [?];PRINT @PrintMessage1;SET @PrintMessage = “U”;select a.id as ObjectID,a.name as IndexName,b.name as TableName, b.modify_date as Stats_Last_Updated_Time from sys.sysindexes as a inner join sys.objects as b on a.id = b.object_id where b.type = @PrintMessage’

    –Note: Before Running this query select the “Results to Text” for proper view.


  54. I have found that you can look at this multiple ways. Here is another useful query:

    WITH last_query_by_db (dbid, Last_query) AS (
    dbid, max(last_execution_time) ‘Last_query’
    from sys.dm_exec_query_stats
    cross apply sys.dm_exec_sql_text(plan_handle)
    group by dbid
    d.name, Last_query
    from sys.databases d
    left outer join last_query_by_db q on q.dbid = d.database_id
    where d.name not in (‘master’,’msdb’,’model’,’tempdb’)
    order by 1

  55. Hi Pinal
    is there any way to display GET_USER_UPDATE at the top of a query result to show When the table was last updated. I can get it to appear in a column in every row, but I want to display it only once. Many thanks.

  56. Please help.

    How can I run this script from a web page. I want to build a report so that my manager will know when each table has been updated.

  57. Hi Pinal,

    For the first time, i’m using replication server.
    i want to know what are all the updates done in Server1, with the user name, date and time.
    i want these details in server 2.
    please help me.

    thanks in advance

  58. You are being asked to login because is used by an account you are not logged into now.
    By logging in you’ll post the following comment to SQL SERVER – Find Last Date Time Updated for Any Table:

    You Can Easily Get the Last Inserted/Updated/Deleted Dates as Follows:

    CREATE FUNCTIOn fn_TablesLastUpdateDate(@Date NVARCHAR(20))

    RETURNS @table TABLE(TableName NVARCHAR(40), LastUpdated Datetime)



    IF(@Date=”) OR (@Date Is Null) OR (@Date=’0′)

    INSERT INTO @table
    ) AS A


    INSERT INTO @table
    ) AS A
    WHERE Date=@Date



    – SELECT * from fn_TablesLastUpdateDate(’06/11/2012′)

  59. Hi All,

    can any one of you please help me on finding that, what is the last type of action happend on particular table whether it is Insert/delete/update

    Thanks in advance

  60. One more qustion, if i run a delete statement like ‘Delete from table1′ and i stopped immeditly(with in 10 seconds) then, is there any chance of deleting the records in table1.
    For me it shows a message like ‘Query batch cancelled’.

    NOTE: Table having more than 1,00,000 of records.

  61. Hi Pinal ,
    i have a requirement where i need to know when a column is updated. i got to know the last modified timestamp of the table and date created but i want to know between a period of time how many times it has updated/deleted rows/inserted . knowing only the last modified is not enough. there are no triggers set on the table nor any audit table is there . i need to trace how many times and when was a particular table modified, i.e history of updates/inserts/deletion. your help is extremely required

  62. Pingback: SQL SERVER – Weekly Series – Memory Lane – #028 | SQL Server Journey with SQL Authority

  63. can u tell me how can i know, if any one user has accessed particular storedprocedure whether by application or from queryanalysis or from any other side.
    Naredra shukla.

  64. @Imran Mohammed,I tried your code with my earlier Databases.it’s not working.this same code I tried with new Database,here it’s working fine.
    Why your code was not working for my earlier databases.

  65. Hi Pinal,

    can you please help as I came across your site.

    I have a table with 3 levels ( level 1 , 2 and 3), I want to set a trigger to help me records the following

    Case ID Caseloadlevel date started being that level date stopped being that level

    I hope i made sense?

  66. Hi,

    It is possible to get Update time of any record?
    I want to get update time of each record.

    Please suggest me how to get it.

  67. Hi Pinal, I work at a bank, and there’s this table (I hate it btw!) with no identities or timestamp columns and I have no permissions to create triggers or temporal tables to check the new records last inserted, so I was wondering if there’s a way to use this method to get the data inserted?

  68. This one is good solution


    — tables modified today
    SELECT [name],create_date,modify_date
    FROM sys.objects
    WHERE modify_date>DATEADD(day,-1,GETDATE())
    AND type=’U’

    — stored procedures modified today
    SELECT [name],create_date,modify_date
    FROM sys.objects
    WHERE modify_date>DATEADD(day,-1,GETDATE())
    AND type=’P’
    You can further modify above queries to get list with objects modified in last 7 days.

    — tables modified in last 7 days
    SELECT [name],create_date,modify_date
    FROM sys.objects
    WHERE modify_date>DATEADD(day,-7,GETDATE())
    AND type=’U’

    • Its a nice solution, but I think the modify_date doesn’t change if you make a INSERT INTO – or a TRUNCATE TABLE – statement

  69. Neat code!

    But this fails on contained databases with error 297 ‘The user does not have permission to perform this action.’ Any ideas which permission to grant to make it work?

  70. Supurb, thank you. However I think you have a tiny issue.
    in your select (SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName…) it should be “AS Table” instead of “AS DataBaseName”.

  71. There is a new scenario . I have a table data like below
    Inumber Stage Description UpdatedDate
    NC0274224 Achieved Customer P5 A Response 2014-04-30 17:01:14.000
    NC0274224 Cancelled Customer P5 A Resolution 2014-05-02 09:06:44.000
    NC0274224 Cancelled HP-ADM P5 Resolution 2014-05-02 09:06:45.000
    INC0164837 Achieved HP-ADM P3 Response 2013-06-13 06:01:36.000
    NC0164837 Cancelled Customer P3 A Resolution 2013-08-20 09:58:53.000
    NC0164837 Breached Customer P5 A Resolution 2014-04-10 05:00:41.000
    NC0164837 Cancelled HP-ADM P3 Resolution 2013-08-20 09:58:53.000I
    NC0164837 Achieved Customer P3 A Response 2013-06-13 06:01:36.000
    NC0164837 Paused HP-ADM P5 Resolution 2013-08-20 09:58:54.000
    i would like to extract the for each “Inumber”, I need to get the stage value based on the latest updated date and group by Resolution / response. Which means, one number may have multiple resolution ( based on Description column) and we need to find out which one is the latest and find the value of stage .
    Pinal , can you /any one helpon thsi .

  72. Hi Pinal,

    A have a scenario similar to this but the table doesn’t have any index so this query is not returning any value.
    Is there any other solution to check the last time a particular table was populated/modified.


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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s