| Author |
Topic |
|
mtrtm
Starting Member
4 Posts |
 Posted - 2005-12-27 : 11:31:38
|
| Hey all,I want to know if there is any way to tell the last time a table has been added or modified using built in SQL Server functionality.I will be using this to confirm when an add/update of a table or columns takes place when pushing changes to a production server. Thanks in advance for any help!Mario |
|
|
bakerjon
Posting Yak Master
145 Posts |
Posted - 2005-12-27 : 11:34:09
|
| I'm not sure I understand the question fully. Are you asking "When was the last time the table was inserted or updated/deleted?", or are you asking "when was the table created or the structure modified?" These are very different questions.Jon-Like a kidney stone, this too shall pass.http://www.sqljunkies.com/weblog/outerjoin |
 |
|
|
mtrtm
Starting Member
4 Posts |
Posted - 2005-12-27 : 11:38:34
|
| Sorry for not being more clear - I want to know "when was the table created or the structure modified" |
|
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2005-12-27 : 12:08:56
|
| Check whether the Select * from SysObjects does any help |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2005-12-27 : 12:13:09
|
quote:
Originally posted by Srinika
Check whether the Select * from SysObjects does any help
if you do an alter table then sysobjects wont help. It only has crdate which is the date the object was created and ALTER does not recreate the object.Daniel, MCP, A+SQL Server DBAwww.dallasteam.com |
|
|
|
mtrtm
Starting Member
4 Posts |
Posted - 2005-12-27 : 12:36:16
|
| Thats what I was afraid of - I found crdate and refdate, but they both contain the same date and seem to be recording when an object was created, not modified. So there are no other options other than implementing something yourself? |
|
|
|
bakerjon
Posting Yak Master
145 Posts |
Posted - 2005-12-27 : 13:45:32
|
| If you are talking about SQL 2000, that is likely the only option you have. I know some people have created triggers on system tables, but I wouldn't do it. If you had SQL 2005, you could do a DDL trigger, which would be much nicer!Jon-Like a kidney stone, this too shall pass.http://www.sqljunkies.com/weblog/outerjoin |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2005-12-27 : 14:03:13
|
quote:
Originally posted by mtrtm
Thats what I was afraid of - I found crdate and refdate, but they both contain the same date and seem to be recording when an object was created, not modified. So there are no other options other than implementing something yourself?
You're other option would be to keep restoring the database backups until you see the old DDL.  You can do that over a new DB but it would surely take a lot of time. If you have SQL Litespeed, you can restore a single table from a backup but I have never tried that option...Daniel, MCP, A+SQL Server DBAwww.dallasteam.com |
|
|
|
mtrtm
Starting Member
4 Posts |
Posted - 2005-12-27 : 15:27:27
|
| Thanks everyone for your help, I will probably just pursue setting up a customized solution.Mario |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-12-28 : 02:04:17
|
| If you run Profiler at that time, you can know this information. Otherwise you need to manually document the changes you are doingMadhivananFailing to plan is Planning to fail |
|
|
|
Kristen
Test
22859 Posts |
|
|
2lazydba
Yak Posting Veteran
62 Posts |
Posted - 2005-12-28 : 16:49:26
|
| no need for dts n stuff..this is cool...http://www.sqlservercentral.com/columnists/jreade/howtoscheduleasqlserverdatabasecreationscript.asp |
|
|
|
|
How to tell the last time a table was modified?