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
Hi Pinal,
Since my server was started one month back, I am getting last_user update (2015-07-31) for a table. That mean my table’s data is not being modified after above date? Can I 100% believe on results return by sys.dm_db_index_usage_stats for Last)user_update?
Please clarify.
Hi Pinal,
This shows last user update, do you know of any way to find out when the last table update was made in general, like through an application? A lot of the tables in my environment get updated through applications, and we are in the process of doing an inventory on old junk tables/records. I need to know the relevance of new updates/inserts in these tables in order to deem them junk or still relevant. Any ideas?
Thank you
Hi Pinal, In one of my table last_user_update is null. what should I believe in this case ? can you please suggest.
Hi Parth,
are you able to get solution for this?
how can i find out all changes made to my specific table. I would like to get the hostname (like the stored procedure change output) who changed my table in sql server. Some one deleted all my entries in a table. That is why i needed this. I think you would help me.
how to know that last updated record from multiple datatabase for today perticular day
Can I find How many rows are inserted in certain table today without having any date column in table?
Hi Pinal,
I have to fire a trigger when a record got modified in above dmv., and store the data in another table. How can this possible. It is possible
Hi Pinal,
I do the columns on “user updates” . May i know number stated is it refer as frequency? What is the range period of this frequency?
Thank you.
I do see* the columns on “user updates” . May i know number stated is it refer as frequency? What is the range period of this frequency?
Thank you.
SELECT
DB_NAME(indexStats.database_id) as databaseName
, OBJECT_NAME(indexStats.”object_id”, indexStats.database_id) as objectName
, sum(indexStats.user_seeks ) as user_seeks
, sum(indexStats.user_scans ) as user_scans
, sum(indexStats.user_lookups ) as user_lookups
, sum(indexStats.user_updates ) as user_updates
, max(indexStats.last_user_seek ) as last_user_seek
, max(indexStats.last_user_scan ) as last_user_scan
, max(indexStats.last_user_lookup) as last_user_lookup
, max(indexStats.last_user_update) as last_user_update
, sum(indexStats.system_seeks ) as system_seeks
, sum(indexStats.system_scans ) as system_scans
, sum(indexStats.system_lookups ) as system_lookups
, sum(indexStats.system_updates ) as system_updates
, max(indexStats.last_system_seek ) as last_system_seek
, max(indexStats.last_system_scan ) as last_system_scan
, max(indexStats.last_system_lookup) as last_system_lookup
, max(indexStats.last_system_update) as last_system_update
FROM sys.dm_db_index_usage_stats as indexStats
group by
indexStats.database_id
, indexStats.”object_id”
order by
indexStats.database_id
, indexStats.”object_id”
;
In my Server I have around 15 databases. For some the records returned by this query I am unable to get the table name. Can any one please help
You can use object_id.
OBJECT_NAME(OBJECT_ID) AS DatabaseName is not correct.
OBJECT_NAME(OBJECT_ID) returns the object name which in your example is the table name.
Thanks for correcting me. I will update it accordingly.
Dear Sir,
I am developing a web application , and need to write a sql query demo satisfy 3nf any change in master table should reflect in all other table where ever that common id shares integrated with PHP and mysql. It would be really helpful if you give me some example that explains clearly.
I am not an design expert in MySql.
I get multiple lines from SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( ‘dbname’)
AND OBJECT_ID=OBJECT_ID(‘tablename’)
each with different index_id:s, none of them being 0, however.
I believe you need to look at the one for the clustered index (id=1). Logically, that should have highest number.
Corrected Code
select object_name(object_id, db_id(‘your database name’)) as TableName, last_user_update
from sys.dm_db_index_usage_stats
where database_id = DB_ID( ‘your database name’) and object_id=(select object_id from your database name.sys.objects where name like ‘your table name’)
Thanks Peter.
This will give last data on which table accessed by uses.
select DISTINCT DB_NAME(database_id)AS ‘DATABASENAME’,object_name(object_id) AS ‘OBJECT_NAME’ ,
MAX(COALESCE(last_user_lookup ,
last_user_scan ,
last_user_seek ,
last_user_update )) [Last time Table Accessed]
from sys.dm_db_index_usage_stats
where database_id =10 —- CHANGE DB_ID NUMBER HERE
and (last_user_lookup IS NOT NULL
OR last_user_scan IS NOT NULL
OR last_user_seek IS NOT NULL
OR last_user_update IS NOT NULL)
GROUP BY DB_NAME(database_id) , object_name(object_id)
Thanks Hemanshu.
Hi Pinal,
when run the above script i get the table access dates as 2 or 3 days before. What about other tables which are accessed one month back or one year back.
When service gets restarted then we will not be able to find the active and inactive tables with this query, how can we handle this?
Thank you, that help me generate a query to find which databases to backup every night!
DECLARE @lastBackupDate DATETIME2
SET @lastBackupDate = GETUTCDATE() – (25.0 / 24.0)
SELECT DISTINCT
‘backup database [‘ + abc.dbname + ‘] … ‘ AS bkpQuery,
abc.dbname
FROM (
SELECT
(SELECT MAX(v) FROM (VALUES (last_user_seek), (last_user_scan), (last_user_update)) AS VALUE(v)) AS maxDate,
d.name as dbname
FROM sys.dm_db_index_usage_stats AS ddius
inner JOIN sys.databases AS d
ON d.database_id = ddius.database_id AND d.database_id > 4) abc
WHERE
abc.maxdate > @lastBackupDate
ORDER BY
abc.dbname
Hi Pinal ,
Please let me know how we can find the last ModifiedOn datetime of a column in SQL SERVER 2008
There is nothing out-of-the-box. You need to use Auditing feature.
this is so awesome, just what i needed
I am glad that it helped you.