Solved

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

Posted on 2013-02-05
8
477 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

696 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