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
@ 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 help, excellent script, thank you.
Thank you Imran Mohammed. This is help me for quick learning the whole DB fast
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.
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