How to know what time a table is last-updated?

Question

Post reply

How to know what time a table is last-updated?

SQL ORACLE

One Orange Chip

Points: 27807
More actions
January 24, 2010 at 10:28 am

#156606

The update can be either table structure or its data. How to know it in SQL 2005?
Many thanks in advance.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You need a trigger or a trace. It's not something that SQL keeps track of itself.

If you can give more detail on what you want, I can give you better suggestions.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

trsjax Valued Member Points: 58 More actions · Answer 2

For changes to the table structure, could you use Modify_Date in sys.object?

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 3

You can, but that's limited info. Doesn't tell you "what" changed. If it's soon enough, the default trace might help you.

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 4

Both sys.tables and sys.objects return the date of modifying table structure, not the date of modifying table data.

arun.sas SSChampion Points: 11831 More actions · Answer 5

SQL ORACLE (1/27/2010)
not the date of modifying table data.

Hi,

Record modified track!!

Unless you follow the Gail approach or set the audit trial/table to the target table or the target table having the last modified date/timestamp columns, it’s not so easy to find out.

WayneS SSC Guru Points: 95460 More actions · Answer 6

As others have already mentioned, sys.tables will show the time the structure of the table was last changed. And you will need a trigger to show when the data was last changed.

So, here's an example:

--make a couple of test tables

if exists (select 1 from sys.tables where name = 'DataMod') drop table dbo.DataMod

go

if exists (select 1 from sys.tables where name = 'DataTest') drop table dbo.DataTest

GO

create table dbo.DataMod([schema] sysname, name sysname, modify_date datetime)

go

create table dbo.DataTest(TestCol1 int identity)

go

-- build a trigger to store off whenever the data is changed on the table

CREATE TRIGGER [trg_data_test] ON dbo.DataTest for update, insert, delete AS

insert into dbo.DataMod select 'dbo','DataTest', GetDate()

GO

-- show when the table was created and modified

select name, create_date, modify_date from sys.tables where object_id = object_id('dbo.DataTest')

GO

-- add three records

insert into dbo.DataTest DEFAULT VALUES

GO 3

-- add a new column to the table

alter table dbo.DataTest add TestCol2 uniqueidentifier default newid()

GO

-- show that the table was modified

select name, create_date, modify_date from sys.tables where object_id = object_id('dbo.DataTest')

GO

-- add three more records

insert into dbo.DataTest DEFAULT VALUES

GO 3

-- show that the data was added, and that the table that stores when the table was last modified has been added

select * from dbo.DataTest

select * from dbo.DataMod

GO

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

mstjean Hall of Fame Points: 3310 More actions · Answer 7

This would NOT catch updates to a non-indexed table and it only has info since the last SQL restart, but if THOSE conditions were not game-stoppers, would this be a viable solution to see data changes?

select

db_name(database_id) dbname,

object_name(object_id,database_id) oname,

MAX(CASE WHEN last_user_update < last_system_update THEN last_system_update ELSE last_user_update END) as LastUpdated

from sys.dm_db_index_usage_stats

group by database_id,object_id

order by db_name(database_id),object_name(object_id,database_id)

Cursors are useful if you don't know SQL

gmby SSCarpal Tunnel Points: 4511 More actions · Answer 8

sys.dm_db_index_usage_stats does report on heaps, so it would include tables without indexes (index_id=0). But as mstjean mentions, the stats are only kept since the last server startup.

jg