Creating a modest Auto Generating SQL change script ?

Question

I'm aware that auto generating SQL change tools exist, however I would like to create a very lightweight tool.

Currently I manually log any stored procedures or table changes (or new stored proc or tables), while this is very effective during publish /release its time consuming (hence while these tools exist)

I just want to automate the sql script generation part.

I'm aware that SQL can be set up to record (or insert a record) when certain database changes occur (this would effectively replace the first manual part of logging the changes), I've found tutorials on how to set this up so I'm ok there.

The part I'd like to know how to do:

Given a table that contains data regarding (new/altered stored procedures and new/altered tables) how can I then generate a SQL script?

can this auto generation be done in SQL (e.g any functions/commands?) or would I have to resort to writing a program to spit out the SQL script?

Many thanks!

Answer 1

I think the best way to handle this kind of thing is to keep an audit log of ddl changes like you mentioned. For instance, in sql server 2005, you can have a trigger fire "AFTER DDL_TABLE_EVENTS" and use the EVENT() function to get the content of the ddl modification (as xml). Suppose you had your ddl modification trigger write to a table that contains the ordered list of modifications that you can replay on other servers - like dev to test to prod. When you're ready to bring the target server up to the same level as the source server, script something - even a sproc or function to dump the ddl changelog table to a text file.