Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 486
  • Last Modified:

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

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
cipriano555
Asked:
cipriano555
  • 4
  • 2
  • 2
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
cipriano555Author Commented:
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
 
Scott PletcherSenior DBACommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
cipriano555Author Commented:
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
 
cipriano555Author Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
cipriano555Author Commented:
Thanks, Scott
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now