July 21, 2006 at 11:34 am
First, I'll give a little historical insight into our process (for a tax software company). Each year, we have tables corresponding to the specific year - i.e. 2005_tblPerson, 2005_tblTaxes, etc.. We then have views and stored procedures related to each of these tables. Most of the views and stored procedures are simply stand-alone objects, but I've been adding more and more dependent views/stored procedures - i.e. viewA uses data from viewB which uses data from viewC. Initially, there were very few of these dependencies and I just kept the list of dependencies in an excel document. For the upcoming year, I will create a 2006_tblPerson, 2006_tblTaxes... (don't need recommendations on how to change this methodology - it is to ingrained into a mountain of code). I then need to create all of the stored procedures/views as well (in their correct order).
The amount of order dependencies has been growing exponentially. I'm thinking about pulling the data out of the excel spread sheet and putting it into database tables. I'm looking for a way to automate the creation of the 2006 tables. Previously, I just used Enterprise Manager to generate all of the creation scripts for me and I would re-order the views/stored procedures into the correct order (using word or text pad - find and replace all 2005 numbers with 2006). It is now so large, I really need a way to programatically convert the tables, stored procedures, and views - either some tool or using some built-in database items. Not only do we perform the udpate, but I also send the scripts to several customers and they implement the same steps.
Any ideas or recommendations (just point me in the right direction)? I'm actually a software developer, but double in my " minutes of free time" as the DBA.
Thanks!
July 21, 2006 at 12:47 pm
SQL DMO is a good way to go.
Do a search the the scripts here you should find one that uses DMO to script out almost any object you want. Just a matter of modifying it a bit to suit your needs at that point.
Cheers,
Wes
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply