I've already posted an example in a previous article, and I'm going to post a couple more on neat things you can do with the DOS for command.

I recently helped out a friend with a method to deploy stored procedures to multiple servers. Previously he was generating the scripts and running them manually in Query Analyzer, for up to 8 different servers. Getting bored and making mistakes, plus needing it to be foolproof for others to use, he was looking for a better way. Here it is. :)

The for command can enumerate through file contents as well as directory listings, so we'll create a plain ol' text file (servers.txt) that contains the SQL Servers we want to deploy to:

server1
server2
server3
server4

The next step is to take our SQL script files (could be one file or many) and put them in a folder. Could be an existing folder or a brand new one, but they should be the only files in it. For this example we'll use the folder c:\scripts.

We're going to use another command-line tool, osql, to actually execute the script files. osql comes with SQL Server 7.0 and higher and is fully documented in Books Online. I'm going to be using these standard flags with it: -S, -E, -d, -n, and -i, for server, trusted connection, database, line numbering off, and input file, respectively. You may need to change some of these for your purposes.

The standard osql command will look like this:

osql -E -n -S server1 -d db1 -i c:\scripts\script1.sql

And this would run script1.sql in database db1 on server1. Naturally we'll want to run this script on every server. The for command takes the switch /F to enumerate through a text file:

for /F %a in (servers.txt) do osql -E -n -S %a -d db1 -i c:\scripts\script1.sql

Notice the %a: this is a variable, which will hold one line at a time from the servers.txt file. We then insert this variable after the -S parameter of the osql command (bolded), which in effect runs this SQL script against each server listed in that file.

Now of course we could have multiple script files in the folder. We could run each one using the standard format of the for command (without switches):

for %z in (c:\scripts\*.*) do osql -E -n -S server1 -d db1 -i %z

This time I used %z as a variable (it will become obvious in a minute), and instead of enumerating the contents of a file, it went through the files in the c:\scripts directory. The %z variable was placed after the -i parameter, which is the script file to execute. So this example ran each script in the folder in database db1 on server1.

So you're wondering how to run ALL script files against ALL SQL Servers in servers.txt? Simple: combine the two for commands into one line:

for /F %a in (servers.txt) do for %z in (c:\scripts\*.*) do osql -E -n -d db1 -S %a -i %z

So how's that? A one-line command that can deploy hundreds of scripts against hundreds of servers. :)