30

The query:

SELECT 
    name AS TableName, 
    create_date AS CreatedDate, 
    modify_date as ModifyDate 
FROM sys.tables 
order by ModifyDate;

...will tell me the last time a table was created and modified (from a DDL perspective). But I want to know the last time actual data was either inserted or removed from the table. Is it possible to get this in SQL Server?

Paul White
  • 83,961
  • 28
  • 402
  • 634
dublintech
  • 1,519
  • 8
  • 21
  • 26

2 Answers2

40

You might be able to get an idea from

SELECT last_user_update
FROM   sys.dm_db_index_usage_stats
WHERE  database_id = db_id()
       AND object_id = object_id('dbo.YourTable')

but the data there is not persisted across service restarts and might not be accurate for your requirements (e.g. running DELETE FROM T WHERE 1=0 will update the time even though no rows were actually deleted)

Is there any way I can run this for all tables in a database at a time, instead of single table?

SELECT last_user_update, t.name
FROM   sys.dm_db_index_usage_stats us
       JOIN sys.tables t
         ON t.object_id = us.object_id
WHERE  database_id = db_id()
Martin Smith
  • 84,644
  • 15
  • 245
  • 333
  • 1
    The last_user_update date is also updated afeter unique index violation exception. Is there any way around this? – Алена Шлыкова Feb 27 '19 at 18:28
  • Why to join with sys.tables? It is enough to use function object_name() only. – Andrey Samykin Nov 30 '21 at 14:22
  • @AndreySamykin for the first query there is absolutely no need for the join to sys.tables. I'll remove it. For the second query joining can have some benefits compared to calling object_name - same as any join can perform better than encapsulating a query in a non inlined scalar UDF and calling that. The function has its own overhead and this approach also forces a nested loops approach. Metadata functions such as object_name also don't respect the isolation level of the outer query so you might want to avoid them if using read uncommitted to avoid blocking – Martin Smith Nov 30 '21 at 15:37
-1

Hi you could add a (hidden) column using the temporal table feature, history table is not required if you just want to track when the last update was. If you want to track deletes you WILL need the history table.

Updated datetime2(7) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,

hat tip to my friend Daniel who introduced me to the idea, a lot more details on his blog here

https://sqlsunday.com/2021/08/09/add-created-and-updated-timestamps/

Stephen Morris - Mo64
  • 4,056
  • 1
  • 9
  • 17
  • 1
    The question wants to find when a table was last updated, your answer seems to answer the question of when a row was last updated. – ypercubeᵀᴹ Nov 30 '21 at 11:53
  • well your just a simple aggregate query away from a correct answer - I guess it depends if you need it to be accurate or you want some sort of estimate – Stephen Morris - Mo64 Nov 30 '21 at 13:12
  • 2
    If a query is needed, I suggest you add it in the answer. – ypercubeᵀᴹ Nov 30 '21 at 13:44
  • The question also mentions removing rows. – Paul White Dec 01 '21 at 08:38
  • While this reply requires the field to have existed in the table from the start and, as others have pointed out, will not detect deleted rows, the accepted answer has the problem that only tables modified since last reboot will have dates. This solution will show older edits. – Erk Feb 02 '24 at 13:01
  • To find the most recent edit date of the whole table one might (not sure about hidden columns) do SELECT MAX(updated) FROM table. – Erk Feb 02 '24 at 13:04