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. 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 finds 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 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 of when was the table last updated. If WHERE condition is entirely removed it will provide details of the entire database.
Reference : Pinal Dave (
http://blog.sqlauthority.com
)
@ Pinal Dave.
Many times I have seen people asking similar question, when was my database/ table last accessed (Tables selected/ inserted / update) . So they can decide if they can drop or take this database offline or drop unwanted tables.
Below script also gives information about those table that were used in select statements, giving information of when a table was last accessed ( selected/Inserted / Updated ).
This is script :
select
t.name
,user_seeks
,user_scans
,user_lookups
,user_updates
,last_user_seek
,last_user_scan
,last_user_lookup
,last_user_update
from
sys.dm_db_index_usage_stats i JOIN
sys.tables t ON (t.object_id = i.object_id)
where
database_id = db_id()
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.
~ IM.
Great Script
I have a question is there any tool or script which shows deleted record of a table.
Cool idea.
The only gotcha is when you run across a table (or set of tables) that has no indexes. of any sort, not even a primary key, let alone anything useful.
Because the DBA before you didn’t think they’d be necessary because “SQL Server does all that for you. It’s set and forget”
Not that I am speaking from experience or anything.
can i use sql server management studio without installing sql server 2005 . bcoz i wnt to pracitice queries. thanks in advance.
If you want to practise queries, install express edition of SQL Server 2005 or 2008
yes you can install only SSMS.
Yes you Can – without a SQL server to connect to, SSMS won’t do anything for you.
Like Madhivanan said, install SQL Express from Microsoft to practice.
@Dave,
Even if you do not have any indexes on your table, you can still execute above script, this should work fine. It will give you correct results.
DMV used is sys.dm_db_index_usage_stats, it is slightly misleading because it does provides details for all table, tables with indexes and with out indexes.
~ IM.
On my machine, the second query only works if “Test” is written with a capital T .
CREATE TABLE Test
SELECT query : (…) AND OBJECT_ID=OBJECT_ID(‘test’)
So, the query is case sensitive…
Very nice Pinal.
Is there a page on your website thA
Very nice Pinal.
Is there a page on your website that lists all the scripts you have (with a link back to the original article)? That would eb a great page to bookmark.
Hi Brian,
Thanks for your help!
I really want to do that project. Honestly but I am sort of time. I am looking for help of volunteer.
I have attempted small list however -
http://www.pinaldave.com/best-sql-blog-sql-server-script.cfm
http://www.pinaldave.com/best-sql-blog-sql-server-script1.cfm
http://www.pinaldave.com/best-sql-blog-sql-server-script2.cfm
Best Regards,
Pinal
Pinal,
That is a good start. It would still need to be categorized.
If i have the free time i may just dig in to it.
Hi Pinal,
We can also see the modifydate in sys.tables view.
select * from sys.tables where type=’u’ and name=’Table_Name’
is this fine to see the modified date for a particular table.
and can you please tell me to see the modified column name and the modified date in a table. because i couldnt see the modify date in sys.columns view for a specific column.
Thanks in advance
The modified date column reflects the changes done on table level. So you can’t track it for column level
Hi,
How about the script to use in sql 2000?
thanks.
How does it work in SQL2000 as Bis has asked?
i need to take offline SQL2000 databases and i am not sure if they are still in production.
I have option to run SQL Trace but i am trying to avoid this since I have 50 + servers for this audit.
Thanks in Adv.
Hi Pinal,
Please help me to find the solution to get the list of table of database where records has inserted, deleted or modified in MS Sql Server 2005.
How can i find the last user who updated the table.I want to know the username?
Help is appreciated.
One option is to use user_name() function as a default value for a column
@Yousuf,
As far as my knowledge for SQL Server goes, SQL Server does not store which user updated or added a record in table.
If you want to do that, you need to add a column Created_By and Last_Updated_By, and put the default value as suser_sname().
When ever a user updates this tables, this values will be recorded in Last_Updated_by column.
You can use SQL Profiler or traces, but these tools give information that is current… They do not give historical information, unless you have set traces that collect auditing information.
~ IM.
Insert the datevalues into the table.
create table dbo.my (datevalue datetime)
declare @time as datetime
set @time=’9/9/1995′
while @time<'9/9/2020'
begin
insert dbo.my values (@time)
set @time=@time+1
end
well very nice stuff….
is there is any way i will find out in SQLServer that the table which is updated in database then on this updated Table on which One or all the Row are effected..
kindly please explain it if we find this info by using this dmv…
Hi Noman,
There is no inbuilt functionality to know which row was changed and what was the changes. But you can design your method to track such details by using triggers, additional timestamp, checksum or datimtime column to check and track changes in a row.
You can also configure traces (profiler) to log all or perticuler changes. As we know, all the changes are completely and sequentially recorded in transaction log but SQL Server does not have any tool to view it. But you can view it using third party tools.
Regards,
Pinal Dave
Hi, I have a database with a table equipment. whose primary key is a datetime that receives contents every 10 seconds for several days.
I need to perform a selection of all data between 2010-01-15 14:02:42.125 and 2010-01-16 14:03:12.126. How do I make this query without separating the days from the hours?
if any of you could give me a hint I would be very grateful.
Can you post some sample data with expected result?
@ Pinal
Hey, This is pretty useful. However for one of the databases that I have, for all the tables in it, the last_user_update is NULL. Why would that be, does it mean that the data hasn’t been captured or does that mean that after the last update, maybe the server was re-started and that info was lost?
Also for a complete newbie, what does the last_user_seek and last_user_scan dates mean and how is it different that the last_user_update?
Thanks,
Divya
Hello Divya,
Is the SQL Server restarted recently? Because every time server restarted the count and date statistics is removed from database.
Te columns last_user_seek, last_user_scan dates and last_user_update are self explanatory. These are last date when index or table was seek, scan or updated.
Regards,
Pinal Dave
Thanks, Pinal. Thats helpful. I am guessing the server was restarted a while back. Now that its all null, is there any other way to know if this database hasnt been used in a while in which case it can be safely retired ?
As for the other databases that did have values for last_user_update, I noticed that for some tables of the database, the column had the time when I actually ran the above query, why would that happen?
Thanks,
Divya
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
It displays when the query did index scan or query did index seek. In case of the large table, index seek is recommended.
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
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
http://beyondrelational.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx
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
http://beyondrelational.com/blogs/madhivanan/archive/2010/07/28/app-name-function.aspx
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
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.
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
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
Pingback: SQL SERVER – Weekly Series – Memory Lane – #028 | SQL Server Journey with SQL Authority
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.