Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-02-05
8
Medium Priority
?
482 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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1000 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 1000 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 70

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

971 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