December 19, 2002 at 12:52 pm
Hi all ...
just wondering if someone knows of a way to figure out when an object (ie. table) was last modified?? (ie. a new column added, index added etc.) There must be a way to use the system tables to get this info??
Thanks,
R.
December 19, 2002 at 1:17 pm
Columns in sysobjects table:
- crdate - date when table was created.
- refdate - not used, reserved for future, currently value is equal to crdate.
- schema_ver - incremented every time schema for a table is changed.
Depending on your needs, schema_ver may be enough. Otherwise... Can you write a trigger on sysobjects and log whatever you want? How would it affect a production system? I never tried it but you could try.
Michael
December 19, 2002 at 2:57 pm
You are not supposed to place any triggers on system objects. They are not guaranteed to work and they can cause unexpected issues.
The best way I have seen most people do is create a table similar to syscolumns, sysindexes, sysobjects and some of the others and scan them daily, if anything is different you mark it however you choose. I know I have put this on my wishlist for the future.
December 19, 2002 at 3:55 pm
I do what Antares suggested when I want to track changes.
Triggers on system tables are not guarentted to fire.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 19, 2002 at 5:19 pm
Again, just write a job that periodically checks for changes in schema_ver column of sysobjects and captures that data in a special user defined table.
Michael
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply