Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bburris
Starting Member
4 Posts |
 Posted - 2005-10-07 : 12:14:06
|
| Is there a table where last DDL change date exists? For instance, if I have added a column to a table is there anywhere I can find when this occurred? Auditors are askng about this. |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-10-07 : 12:21:47
|
| < 2000 NO= 2005 YESYou have to do this via some form of change control that you manageEDIT: How did you make the change? In Enterprise Manager or through a script in Query Analyzer or osql?Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-07 : 12:24:42
|
| Nope. Your best bet is to script out the whole database (e.g. overnight) and store the files in a Version Control system.http://www.mindsdoor.net/DMO/DMOScriptAllDatabases.htmlAlternatively you could copy the system tables to your own "shadow" tables (daily / hourly / whatever), only those rows that have changed, and keep a history of previous values.Kristen |
|
|
|
bburris
Starting Member
4 Posts |
Posted - 2005-10-07 : 12:32:58
|
| Thanks for the responses. I was hoping SQL Server itself tracked DDL changes. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-07 : 12:51:47
|
| You could restore backups and successively compare - e.g. if you've got a fraud to investigate.Going forwards scripting the database out is your best way, although as Brett has reminded me you can put a "trigger" on the system tables in 2005 [it isn't exactly like a trigger though IIRC] Kristen |
|
|
|
mriverol
Starting Member
10 Posts |
Posted - 2005-10-08 : 15:59:20
|
| You could get a copy of Red Gate - SQL Compare. This enables you to compare all objects from two databases. Additionally, you can create a snapshot of a database schema and save it to a file. This snapshot file can then be used as a source for comparison with your current database. |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2005-10-08 : 23:20:50
|
quote:
Originally posted by Kristen
Nope. Your best bet is to script out the whole database (e.g. overnight) and store the files in a Version Control system.http://www.mindsdoor.net/DMO/DMOScriptAllDatabases.htmlAlternatively you could copy the system tables to your own "shadow" tables (daily / hourly / whatever), only those rows that have changed, and keep a history of previous values.Kristen
EDIT: Sorry about this post, my 2 year old did it...not a bad way to boost post count... Say something nice Fletcher..Fletcher: "NULLs suck!"Dad: "That's my boy!"DavidMA front-end is something that tries to violate a back-end. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-10 : 01:24:05
|
 |
|
|
|
|
|
|
Tracking object change dates