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
Nice useful post . But what about large amount of tables are getting updated every day?
Do you have any script for that ?
I need now , how to get datetime informations from my last update table of Studenti?
I need for all the databases at a time..for last update statistics
Thank You Pinal
–To view all last updated tables of all databases,
Execute sp_msforeachdb @command1=’DECLARE @PrintMessage NVARCHAR(50);SET @PrintMessage = “USE [?] Database Name”;USE [?];PRINT @PrintMessage;select top 5 Modify_Date as Last_Modified_Date,Create_Date,Name ,Type_Desc as Table_Name from sys.tables order by Modify_Date Desc’;
–To view all the databases at a time..for last update statistics
Execute sp_msforeachdb @command1= ‘DECLARE @PrintMessage NVARCHAR(50),@PrintMessage1 NVARCHAR(50);SET @PrintMessage1 = “USE [?] Database Name”;USE [?];PRINT @PrintMessage1;SET @PrintMessage = “U”;select a.id as ObjectID,a.name as IndexName,b.name as TableName, b.modify_date as Stats_Last_Updated_Time from sys.sysindexes as a inner join sys.objects as b on a.id = b.object_id where b.type = @PrintMessage’
–Note: Before Running this query select the “Results to Text” for proper view.
M.I
I have found that you can look at this multiple ways. Here is another useful query:
WITH last_query_by_db (dbid, Last_query) AS (
select
dbid, max(last_execution_time) ‘Last_query’
from sys.dm_exec_query_stats
cross apply sys.dm_exec_sql_text(plan_handle)
group by dbid
)
select
d.name, Last_query
from sys.databases d
left outer join last_query_by_db q on q.dbid = d.database_id
where d.name not in (‘master’,’msdb’,’model’,’tempdb’)
order by 1
Thank you very much for aligning this code! Very appreciated!
how find last delete command used by which user
Hi Pinal
is there any way to display GET_USER_UPDATE at the top of a query result to show When the table was last updated. I can get it to appear in a column in every row, but I want to display it only once. Many thanks.
Tombo
Please help.
How can I run this script from a web page. I want to build a report so that my manager will know when each table has been updated.
You can use connection string method to connect to SQL Server and call that query ; get the resultset and process in web page
great script sir…..!
Hi Pinal,
For the first time, i’m using replication server.
i want to know what are all the updates done in Server1, with the user name, date and time.
i want these details in server 2.
please help me.
thanks in advance
thanks a lot
Thank you very much,it save my lot of time.
How
get identify who had change in database ??
This one looks ok –
SELECT name, create_date, modify_date FROM DB_Name.sys.tables
This did the trick! Thanks
thank you very much for info.
You are being asked to login because is used by an account you are not logged into now.
By logging in you’ll post the following comment to SQL SERVER – Find Last Date Time Updated for Any Table:
You Can Easily Get the Last Inserted/Updated/Deleted Dates as Follows:
CREATE FUNCTIOn fn_TablesLastUpdateDate(@Date NVARCHAR(20))
RETURNS @table TABLE(TableName NVARCHAR(40), LastUpdated Datetime)
AS
BEGIN
IF(@Date=”) OR (@Date Is Null) OR (@Date=’0′)
BEGIN
INSERT INTO @table
SELECT TOP 100 PERCENT TABLENAME,LASTUPDATED FROM
(
SELECT B.NAME AS ‘TABLENAME’, MAX(STATS_DATE (ID,INDID)) AS LASTUPDATED
FROM SYS.SYSINDEXES AS A
INNER JOIN SYS.OBJECTS AS B ON A.ID = B.OBJECT_ID
WHERE B.TYPE = ‘U’ AND STATS_DATE (ID,INDID) IS NOT NULL
GROUP BY B.NAME
) AS A
ORDER BY LASTUPDATED DESC
END
ELSE
BEGIN
INSERT INTO @table
SELECT TOP 100 PERCENT TABLENAME,LASTUPDATED FROM
(
SELECT B.NAME AS ‘TABLENAME’, MAX(STATS_DATE (ID,INDID)) AS LASTUPDATED,
CONVERT(VARCHAR, MAX(STATS_DATE (ID,INDID)), 103) as Date
FROM SYS.SYSINDEXES AS A
INNER JOIN SYS.OBJECTS AS B ON A.ID = B.OBJECT_ID
WHERE B.TYPE = ‘U’ AND STATS_DATE (ID,INDID) IS NOT NULL
GROUP BY B.NAME
) AS A
WHERE Date=@Date
ORDER BY LASTUPDATED DESC
END
RETURN
END
– SELECT * from fn_TablesLastUpdateDate(’06/11/2012′)
Hi All,
can any one of you please help me on finding that, what is the last type of action happend on particular table whether it is Insert/delete/update
Thanks in advance
hari