Toad World® Forums

Multiple databases need identical alter script

hi, for some years now I have been doing the same set of procedures with tdm. I love the tool but I think there must be a better way for me to use the alter scripts. I have a scenario where we have lots of clients all using different databases but they all have identical structures and some records that are common to all clients. All clients use the same website - just the name of the database changes. The project is growing rapidly so we are making database changes often. Every time I make a change, I create an alter script for one of the databases, then go into my MS SQL server and open a new query on each database, copy the alter script to that query and run it. This works fine. When we were maintaining just a few databases it was fast and convenient, but with dozens of databases this has now become time consuming. How do other people manage this? I am not really a SQL boffin. I know how to design and use the databases, but sql scripter - I am not . Can anybody suggest a better path for me? Any suggestions would be appreciated.

Does the SQL code actually change from database to database? Do you need to change anything in the code for the name change?

No - I use the exact same alterscript from TDM. No changes - I just need to apply that script to many databases

One thought that comes to mind would be to use something like Toad for SQL Server’s Group Execute function where you could execute the same script across multiple instances/databases. Here is a page with some more information on that feature: www.toadworld.com/…/10587.group-execute.aspx.

compile your scripts in target environments using sqlcmd client from a batch file. You can use command line parameters to fill in the target server and db name at run time:

for example this batch line executes all .sql files in the local directory in the target server and db:

for %%G in (*.sql) do sqlcmd /S %1 /d %2 -E -i"%%G"

NOTE THE ABOVE CODE IS NOT TESTED, JUST OFF TOP OFF HEAD, BUT YOU GET THE IDEA…

Kevin - it looks like the Group Execute in toad for SQL Servers is precisely what I need, but unfortunately, I will not convince management to drop $700 on this.

KJAmbrose - I will certainly investigate this suggestion - it seems that wil lbe all I need.

Thank you for your time guys