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.
Reference : Pinal Dave (https://blog.sqlauthority.com)
189 Comments. Leave new
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.
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
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.
@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.
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?
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.
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?
This one is good solution
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
Thanks Dave, for your always concise and accurate information. Very useful
Hi,
Is there any way to track the DDL changes in the database without Triggers.?
One way is to make use of traces
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?
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”.
‘View Server State’ needs to be granted for this dmv to work. Not a good idea in a production environment that is shared with other databases.
Thanx for help me…! :)
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 .
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.
Thanks
Ankit
Nice code man, thank you
You may give a lot of permission problem with this technique.
Simple you can use:
SELECT name, [modify_date] FROM sys.tables
very nice and helpful article :)
how to get latest inserted row (not update) of specific table?