Table last updated??
-
Hello,
I was wondering if there is a way in T-SQL to write a (simple) query to find out when a particular table was last updated. I would greatlly appreicate if anyone has any insight into this.
thank you,
V
-
Not unless you've got some form of datetime field that's set to the current date on an update.
Timestamp columns are updated whenever a record is changed, but I don't think it's possible to work them to a date
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Timestamp is NOT a date or time, but rather a semi-sequential binary incremental number that is unique database wide. Infact in SQL 2000 timestamp is more correctly known as rowversion.
As far as last time a table was updated... You need to set a trigger or some method that automatically records that somewhere for you. SQL Server can no more tell you when a Table was updated then it could tell you when a Row was last updated.
Julian Kuiters
juliankuiters.id.au -
couple thoughts:
Whatever is updating the data can set a datetime field to GETDATE() either in the actual table or in a secondary table
If you use a trigger to update a field in the table then make sure the trigger is not for replication (see BOL))
Everett Wilson
ewilson10@yahoo.com -
Hi, if u'r using SQL 2000 then u can use the field type TIMESTAMP, that type is refresh automatically by SQL with every operation in the table.
Grettings, Juan Ruben
SQL DBA, Cuba
-
True, TIMESTAMP columns are altered everytime an insert or update occurs in a row, however, they are NOT a datetime value.
A TIMESTAMP column can tell you in what order rows have been updated, what rows were updated after a particular row. You could tell that row211 was updated after row411.
But they cannot tell you the datetime of the last update. You cannot query the TIMESTAMP column to say "my table was last updated on 2004-09-21 at 9:35:04AM". You need a datetime column, with a trigger, or good sprocs to be able to track this.
Becuase of this confusion, and the conflict with the SQL-92 specification of TIMESTAMP, microsoft are changing the TIMESTAMP to known as ROWVERSION.
Julian Kuiters
juliankuiters.id.au -
Thank you guys, I really appreciate all your comments and suggestions on my post. I was just wondering if there was a quick and dirty way of doing this, but looks like I will have to sit down and write some triggers for my tables
.anyways, thanks once again for all your help,
V
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply