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
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.
Thanks
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’)
AND OBJECT_ID=OBJECT_ID(‘test’)
Note that this code will work from version 2005 onwards only
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]
AS
BEGIN
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
END
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.
Regards,
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
Kathis
Hi,
I was wondering if it is possible to check which table’s field was last created, updated.
Thanks,
You can track this in Sys.objects view.
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
Renju
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?
Thanks,
Paresh
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.
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.
Diane
hi pinal,
what is the different between last_user_seek dan last_user_scan?
thanks a lot
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’)
AND OBJECT_ID=OBJECT_ID(‘test’)
Running query provides accurate details
Regards
sharad patil
Note that this code will not work versions prior to 2005
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.
Regards,
Charles Mutunga.
Here is what I used:
USE AdventureWorks
GO
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’
I have a question is there any tool or script which shows deleted record of a table.
Search for “log reader” in Google/bing
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.
Regards,
Jayesh G
Thanks Pinal. you simply rock.
very nice article. you are really a life savior
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’.
why?
How can I solve it ?
Note that this object is available from version 2005 onwards only
It does not work in SQL Server 2008. The above procedure does not exists.
Have you tried this?
select * from sys.dm_db_index_usage_stats
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?
Thanks
Majella
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.
Make use of a trigger
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.
Thanks
Koushik
Another method would be to use timestamp datatype and moniter it’s value
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,
AK
Without the columns like datetime, timestamp it is not possible