Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to know what time a table is last-updated? Expand / Collapse
Author
Message
Posted Sunday, January 24, 2010 10:28 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, April 5, 2013 4:43 PM
Points: 1,473, Visits: 1,314
The update can be either table structure or its data. How to know it in SQL 2005?

Many thanks in advance.
Post #852747
Posted Sunday, January 24, 2010 11:10 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:09 PM
Points: 40,172, Visits: 36,560
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 2008, MVP
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

Post #852757
Posted Monday, January 25, 2010 7:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, October 12, 2014 8:56 AM
Points: 10, Visits: 55
For changes to the table structure, could you use Modify_Date in sys.object?
Post #852976
Posted Monday, January 25, 2010 8:31 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 4:56 PM
Points: 31,168, Visits: 15,612
You can, but that's limited info. Doesn't tell you "what" changed. If it's soon enough, the default trace might help you.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #853045
Posted Wednesday, January 27, 2010 8:30 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, April 5, 2013 4:43 PM
Points: 1,473, Visits: 1,314
Both sys.tables and sys.objects return the date of modifying table structure, not the date of modifying table data.
Post #854880
Posted Wednesday, January 27, 2010 8:47 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 19, 2012 10:25 PM
Points: 1,229, Visits: 3,483
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.
Post #854884
Posted Wednesday, January 27, 2010 9:58 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:46 AM
Points: 5,364, Visits: 8,948
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #854897
Posted Monday, February 1, 2010 7:07 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, October 9, 2014 5:11 PM
Points: 436, Visits: 2,293
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
Post #857679
Posted Tuesday, February 2, 2010 9:23 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:23 AM
Points: 1,738, Visits: 3,399
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
Post #858055
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse