How To Find Last Modified Table In Sql Server 2012

Question 1

Y'all 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 at that place is non persisted across service restarts and might not exist authentic for your requirements (due east.g. running DELETE FROM T WHERE i=0 will update the time even though no rows were really deleted)

Is in that location any mode I tin run this for all tables in a database at a time, instead of unmarried table?

                SELECT last_user_update, t.name FROM   sys.dm_db_index_usage_stats the states        Bring together sys.tables t          ON t.object_id = us.object_id WHERE  database_id = db_id()

Question 2

Hi you lot could add a (subconscious) column using the temporal tabular array feature, history table is not required if you just want to track when the last update was. If you lot want to rails deletes you Volition demand the history table.

Updated datetime2(7) GENERATED Always Equally ROW START Subconscious Not NULL,

chapeau tip to my friend Daniel who introduced me to the idea, a lot more details on his web log hither

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

Accepted Answer · 2012-02-13 10:05:13Z

Y'all 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 at that place is non persisted across service restarts and might not exist authentic for your requirements (due east.g. running DELETE FROM T WHERE i=0 will update the time even though no rows were really deleted)

Is in that location any mode I tin run this for all tables in a database at a time, instead of unmarried table?

                SELECT last_user_update, t.name FROM   sys.dm_db_index_usage_stats the states        Bring together sys.tables t          ON t.object_id = us.object_id WHERE  database_id = db_id()

answered Feb 13, 2012 at 10:05

Martin SmithMartin Smith

77.9k fifteen gold badges 225 argent badges 316 bronze badges

iii

The last_user_update date is also updated afeter unique index violation exception. Is there whatsoever fashion around this?

February 27, 2019 at eighteen:28
Why to join with sys.tables? It is enough to utilize part object_name() only.

Nov xxx, 2021 at 14:22
@AndreySamykin for the get-go query there is admittedly no demand for the bring together to sys.tables. I'll remove it. For the second query joining tin can have some benefits compared to calling object_name - aforementioned as any join can perform improve than encapsulating a query in a not inlined scalar UDF and calling that. The function has its own overhead and this approach also forces a nested loops arroyo. Metadata functions such as object_name also don't respect the isolation level of the outer query so you lot might desire to avoid them if using read uncommitted to avert blocking

Nov xxx, 2021 at xv:37

Answer 2 · 2021-11-30 10:21:53Z

Hi you lot could add a (subconscious) column using the temporal tabular array feature, history table is not required if you just want to track when the last update was. If you lot want to rails deletes you Volition demand the history table.

Updated datetime2(7) GENERATED Always Equally ROW START Subconscious Not NULL,

chapeau tip to my friend Daniel who introduced me to the idea, a lot more details on his web log hither

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

answered Nov 30, 2021 at x:21

4

The question wants to detect when a table was last updated, your answer seems to answer the question of when a row was last updated.

November 30, 2021 at eleven:53
well your just a unproblematic aggregate query away from a correct respond - I guess it depends if yous demand it to be accurate or you want some sort of estimate

Nov xxx, 2021 at xiii:12
If a query is needed, I suggest you add it in the respond.

November 30, 2021 at 13:44
The question also mentions removing rows.

Dec 1, 2021 at 8:38