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)
pinaldave
@ 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.
@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
Hi,
How about the script to use in sql 2000?
thanks.
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.
@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