Solved

How to find update dates for all tables in SQL Server 2005

Posted on 2013-02-05
8
466 Views
Last Modified: 2013-02-09
How can I query (find out) the last time data in a Sql Server 2005 table was modified?  What if I want the query to cycle through every table in a database and for each table tell the date/time of last data modification?  Or even do that for all databases in the installation.

Thanks
0
Comment
Question by:cipriano555
  • 4
  • 2
  • 2
8 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 38856792
SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,DB_NAME( database_id)
 FROM sys.dm_db_index_usage_stats
-- WHERE database_id = DB_ID( 'YourDBName')
0
 

Author Comment

by:cipriano555
ID: 38860252
Didn't seem to give the information I was seeking.  I have a database called SALES so, I tried this

SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,DB_NAME( database_id)
 FROM sys.dm_db_index_usage_stats
 WHERE database_id = DB_ID( 'SALES')

Under the column, DatabaseName,  I got values such as sysowners, and sysrowsets, and NULL, but none of the values were SALES, and there were no recognizable table names.  Does a value for OBJECT_ID need to be set?

I want to be able to say, column Total_charges from Sales was last updated 10/19/2012.
So I need the database name, the column name, and the date, and the query you supplied didn't give that.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 250 total points
ID: 38860268
SQL Server doesn't keep that information by default.  After all, it would be a huge amount of info, and might never be needed.  Thus, you have to put something in place to capture that info if you need it.
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 250 total points
ID: 38860283
forgot to mention,
since the above query is fetching the data from DMV which can store the data only from the last sql service start, the data wont be 100% accurate.
Also there was a typo in my query, 'databasename' should be 'tableaname'

here is the query I have tested

use tempdb
go

create table dbo.tmp (i int )
go

SELECT OBJECT_NAME(OBJECT_ID) AS TableName, last_user_update,DB_NAME( database_id)
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'tempdb')

go
insert into dbo.tmp select 1
go

SELECT OBJECT_NAME(OBJECT_ID) AS TableName, last_user_update,DB_NAME( database_id)
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'tempdb')
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:cipriano555
ID: 38868248
Scott:
I need that information.  So if it doesn't keep it by default, then is there some switch I can turn on to make it keep the information?  (i.e. not use the default.)  It is one row of data per table.  Why is that "huge".  My tables may have many millions of rows, so I would consider one row very small.

Anee: I'm going to assume DMV does not mean Department of Motor Vehicles, but something else that I am not aware of.

The query you posted seems like it should work, but when I use it, nothing is returned when I put my database name in,  DB_ID('Sales').  If I leave off the WHERE condition, I get data returned for many tables, but not including the one I want, and the database listed is not the database where the tables that are list are found.  For example, it lists several of my tables as being in msdb, which they are not in that data base.  I did check to make sure there wasn't a duplicate copy there.

So amazing, that simple information like this is hard to get.  I guess I could put a trigger on every table and collect the information manually, if need be.  But I want to use built-in functionality if it is there.
0
 

Author Comment

by:cipriano555
ID: 38868425
The Bottom Line:

Not as simple as one would expect.  The table, sys.dm_db_index_usage_stats, will only contain the information if the table has an index defined on.  Also, that information is blanked out everytime sql server is restarted!

You can define a trigger on any table you want to capture this information for and store a timestamp in a table, or put datetime column in the table.  But probably the best way to do it is using a feature called Change Data Capture, CDC.  This was designed specifically to make this simple, and so that you can avoid “expensive” solutions to getting this information.  This was first implemented in sql server 2008 and sql server 2005 does not have this feature.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38869022
Easiest would be to add a trigger for every table that needs to be tracked which updates a control table giving the datetime of that trigger action.  I would pre-insert all rows into the control table.  If you prefer, you could instead change the code to INSERT if the row did not exist, add the row if it did ... but if you do that, do it efficiently, because trigger should run as quickly as possible to avoid delaying all SQL activity on that table.


For example:


CREATE TRIGGER dbo.tablename__trg_capture_datetime
ON dbo.tablename
AFTER DELETE, INSERT, UPDATE
AS
SET NOCOUNT ON
DECLARE @action char(6)

IF EXISTS(SELECT TOP (1) 1 FROM inserted)
    IF EXISTS(SELECT TOP (1) 1 FROM deleted)
        SET @action = 'UPDATE'
    ELSE
        SET @action = 'INSERT'
ELSE
    SET @action = 'DELETE'

UPDATE dbo.control_table
SET
    deleted_date = CASE WHEN @action = 'DELETE' THEN GETDATE() ELSE deleted_date END,
    inserted_date = CASE WHEN @action = 'INSERT' THEN GETDATE() ELSE inserted_date END,
    updated_date = CASE WHEN @action = 'UPDATE' THEN GETDATE() ELSE updated_date END
WHERE
    table_name = 'tablename'
GO


--Cluster the control table on "table_name".
0
 

Author Comment

by:cipriano555
ID: 38871404
Thanks, Scott
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now