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 |
|
speno
Starting Member
18 Posts |
 Posted - 2004-12-29 : 13:37:32
|
| I need help constructing an sql loop statement that can be run to alter objects of similar name on multiple databases with one statment. For example, I have a dev server with 4 databases that have many identical tables, views, stored procs. Any change to a table in one database has to be done in the other 3. Any change on any of the dev databases has to then be made on the production server for all 4 databases there. So the statment is run 8 times. Having to run this once would be ideal. |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-12-29 : 14:03:00
|
| For things like this, I use SQL Compare by Red Gatehttp://www.red-gate.comMichael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
 |
|
|
speno
Starting Member
18 Posts |
Posted - 2004-12-29 : 14:12:18
|
| I have that product and like it. But sometimes it's just quicker to do it in query analyzer for a single object. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-12-29 : 15:28:56
|
| Just put the changes in a script file and use osql to run it against the databases.You can run the osql commands from t-sql or a bat file.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-12-29 : 17:13:32
|
| If you know the object that changed, just select that object to compare in SQL Compare. Should take no-time. Once you get the script it generates, do what Nigel says above.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-12-29 : 17:31:15
|
| In case you aren't familiar with osql, it's a command line utility. So save your scripts in a file. Then from a cmd window:osql -SserverName -E -iC:\temp\SomeScriptFile.sql -ddbName1 -o>C:\temp\Output.txtosql -SserverName -E -iC:\temp\SomeScriptFile.sql -ddbName2 -o>>C:\temp\Output.txtosql -SserverName -E -iC:\temp\SomeScriptFile.sql -ddbName3 -o>>C:\temp\Output.txtTara |
|
|
|
speno
Starting Member
18 Posts |
Posted - 2004-12-30 : 14:30:48
|
| Thanks for those replies. I will certainly give them a try but I would still like to know how to save a script that already has the multiple databases and server names as parameters along with the loop statement so that all I have to do is open that script and then insert the alter or create statement between the loop and let it rip thru all 8 databases (4 databases each on 2 servers). |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-12-30 : 14:35:45
|
| Have you looked at WHILE in SQL Server Books Online? You might have problems though if you need to do GOs as that'll escape out of the loop.Tara |
|
|
|
|
|
|
Construct Loop to alter multiple databases