SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
SQL ORACLE
SQL ORACLE
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6575 Visits: 1314
The update can be either table structure or its data. How to know it in SQL 2005?

Many thanks in advance.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216361 Visits: 46277
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
trsjax
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 63
For changes to the table structure, could you use Modify_Date in sys.object?
Steve Jones
Steve Jones
SSC Guru
SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)

Group: Administrators
Points: 142086 Visits: 19421
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
My Blog: www.voiceofthedba.com
SQL ORACLE
SQL ORACLE
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6575 Visits: 1314
Both sys.tables and sys.objects return the date of modifying table structure, not the date of modifying table data.
arun.sas
arun.sas
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2965 Visits: 3493
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
WayneS
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20882 Visits: 10652
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, 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

mstjean
mstjean
SSC Eights!
SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)

Group: General Forum Members
Points: 911 Visits: 2550
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
gmby
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2555 Visits: 3805
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search