This is Part 2 of the series on the for command. In an earlier article I covered using for to deploy SQL scripts against multiple servers. I'm going to enhance this a little by specifying multiple databases as well, and generating server and database lists automatically. I'll also add a little enhancement for deploying scripts in a particular order of execution.

In the previous article I created a text file (servers.txt) that contained the names of the SQL Servers against which a number of scripts needed to be deployed. The assumption was that there was only a single database (with the same name) on each server. If we need to deploy these scripts on multiple databases on each server, we have a few options.

One option is to create another text file (databases.txt) containing the names of the databases we need:

database1
database2
myFriendlyDBName

And use another set of nested for commands:

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

While this works, I'm sure you've noticed that the command line is getting pretty long, and as any good programmer will tell you, nested loops are not a good thing to have. We also have a possibility that not all databases will exist on each server; this isn't a big problem, as osql will throw an error, do nothing, and the for loop will continue anyway. However, a bigger problem would be that we don't want to deploy to ALL databases on ALL servers, but only specific combinations.

The solution would be to use a different file (serverdbs.txt) that contain each valid combination of server and database we want to deploy to:

server1,db1
server1,db2
server2,db2
server3,db3

You'll note that I've delimited the server and database names with a comma (,) although I could use any character as a delimiter. I personally prefer to use tabs, for various reasons I won't go into now, but for these articles I will use non-tab delimiters so as to avoid the problems of whitespace in HTML. I may also mix and match different delimiters in the same file. The for command can identify multiple delimiters and tokens (parameters) within a line of text:

for /F "tokens=1,2 delims=," %a in (serverdbs.txt) do osql -E -n -S %a -d %b -i c:\scripts\script1.sql

Notice the "tokens" section I've added, and also notice the %b. While I didn't specify %b explicitly, I specified "tokens=1,2". This tells the for command to parse the line (using comma as the delimiter) and use tokens 1 and 2 from those results. Since I specified %a as the parameter, the first token went into %a, and the second into %b. If I had specified %b, for would parse out to %b and %c instead. This can be very flexible but you should pay close attention to the variable names you use if you are going to nest for commands. That's why you'll often see me use %z for certain things, I know I will only be parsing a single token there, and it won't interfere with other for commands.

So now that we have two variables in one file, our command-line becomes a little neater:

for /F "tokens=1,2 delims=," %a in (serverdbs.txt) do for %z in (c:\scripts\*.*) do osql -E -n -S %a -d %b -i %z

If you recall from the previous article, I was helping a friend to create a deployment solution. One factor that came up was that the scripts had to run in a certain order. When using the for command against a fileset, you may not get the files in the order you expect. You could change the file name so that they are correctly ordered, but an easier way is to...you guessed it, put them into another text file (scripts.txt) in the proper order:

changeTable1.sql
changeTable2.sql
trigger1.sql
procedure1.sql
procedure2.sql

Of course I've numbered these for illustration, you can name them any way you like (or keep the names you already have). The new command line becomes:

for /F "tokens=1,2 delims=," %a in (serverdbs.txt) do for /F %z in (scripts.txt) do osql -E -n -S %a -d %b -i c:\scripts\%z

You'll notice I've added the /F switch to the 2nd for command, because we're now using a file instead of a directory listing. I also added the script file path (c:\scripts) between the -i switch and the %z variable. This is because the scripts.txt file does not contain the path as part of the file names. If it did, you could remove the path from the command line. If the path is not included, osql won't find the script file and will throw an error.

I know this article is pretty long, but I'm going to finish it with a nice feature. :) You've probably been wondering if there's a way to generate these supporting files (scripts.txt, servers.txt, etc.) automatically. It would be time-consuming to have to create them in an environment of several hundred servers and/or databases. Fortunately osql can help us out; it has a switch -L that will list all servers visible on the network. I'm going to add some DOS redirection (> directive) to put the output into a new file:

osql -L >servers.txt

You now have a file (servers.txt) with all your available server names. You'll notice the first line contains "Servers:" as a heading; we'll use some simple commands to ignore it later.

If you're saying, "well that's nice, but I need all the databases on those servers too!", you're going to get them. For this example I'm going to assume I need to run these scripts against all non-system databases on each server. I'll use a for command on the file osql just created:

for /F %z in (servers.txt) do 
if "%z" neq "Servers:" 
osql -Q"set nocount on;select left('%z,'+rtrim(name), 80) from master..sysdatabases where dbid>4" -E -n -h-1 -w80 -S %z >>serverdbs.txt

OK, there's a lot going on in that one. I've broken it up into multiple lines, in reality it must be run as one command on one line.

The first difference is the if command: this works just like it would in any programming language. In this case, the "NEQ" operator is used to test for inequality. I'm testing the %z variable to see if it matches the header line that osql -L creates. If it does, it fails the inequality, and the rest of the command is not executed.

For those lines that do pass the test, they'll run the osql command that follows the if command. It's identical to the earlier example that ran a single script against multiple servers, except that instead of a script file, we're using a SQL query via the -Q flag. I had to use SET NOCOUNT ON to keep the row count out of the results. I also used the -h-1 flag to eliminate the column headings and separator, and -w80 to format the output to 80-character lines. That's also why I used left(..., 80) in the query. I'm trying to get output exactly like what was shown previously in the serverdbs.txt file. osql's standard output would include spaces and such that would compromise that.

Finally, I redirected osql's output to the serverdbs.txt file, using the append (>>) directive instead of the regular >. This prevents the serverdbs.txt file being overwritten by each loop through the for command. Normally, whenever I'm outputting data from a database I prefer to use bcp; however bcp ALWAYS overwrites the destination file, and the workaround to make it append would be too complicated. In any case, you now have a serverdbs.txt file you can use as is, or edit to remove server/database combinations you don't want.