Solved

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

Posted on 2013-02-05
8
475 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:Scott Pletcher
Scott Pletcher 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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
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
 

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:Scott Pletcher
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

726 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