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
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.
Thanks
Not possible without using a datetime column with default value of getdate()
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
Refer this post
Try this
Nice script … few changes
1) Minor Error: I think it is ObjectName (not database name)
SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName …
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. ”
Regards,
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
Thanks,
Mary
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?
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
You can make use of app_name() function. Refer this for more informations
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.
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.
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….
It is not possible unless you habe documented the changes or put the code in VSS
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..
select current_date from table
where srno=your_no
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.
Regards,
Jayesh G
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.
There are two ways.
1 Have an update trigger on the users table and track the informations
2 Run a profile and trace the activities on that table
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….
If you want to update it for a single row, use update statement. If you want to have it as a default value, alter the table and have default definition on that column
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?
It does not matter. It records data only when the server is on
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.
Did you ever find out what permissions are needed? I ran into the same error after migrating from 2005 to 2008.
well done!@
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?
thanks.
There is no way until you have run the profiler to trace the changes
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?
Default option is what is needed in this case. Why do you want to avoid default option?
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.)
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 !
Thanks