Preface

Have you ever wanted a simple and effective tool that can execute a bunch of scripts from a folder? If you have, and did not want to use 3^rd party tools for getting the job done, you have come to the right place.

First of all, I would Thank Jason Selburg for his method of executing the scripts which made me put this Contribution as one more method of simplifying your daily task. You can see his article on SQL CMD Mode for more information.

Problem Statement

As part of a development team, your daily work includes fixing bugs and adding new changes to SQL scripts. You also need to check that code into your Version Control System (VCS). That means every time another developer changes any script, you would need to execute the same script on your database so that it is up-to date. You need to do this at least every day, if not every couple of hours.

So once you have the latest script, you can use this cool trick to update multiple databases in one go. You will need two folders with scripts. For my illustration, I have two folders called:

1. Create Scripts
2. Change Scripts

You can see this in the image below.

Each folder has its own set of scripts:

Now the Magic

Open your favorite text Editor or Notepad, if you prefer, and enter the following:

@@echo off
del errors /f /s /q
rd Errors
md Errors
FOR %%A IN (*.SQL) DO ( sqlcmd -S SERVERNAME -d DATABASE1 -U username -P password -i "%%A" -o "Errors\%%AError_DB1.txt" -I )
FOR %%A IN (*.SQL) DO ( sqlcmd -S SERVERNAME -d DATABASE2 -U username -P password -i "%%A" -o "Errors\%%AError_DB2.txt" -I )

You would need to change the following as per your Database Name(s) and credentials in the above script:

• SERVERNAME – Your Database Server Name.
• DATABASE1 – Your Database name.
• Username – Your SQL Username.
• Password – Your SQL Password.

Save this file with the Extension as .bat in one of your scripts folder.

Copy this batch file to the other script folder.

The Explanation

Here is an explanation of each section of the code.

del errors /f /s /q

When executing the scripts, we dump all errors to a folder called Errors. This needs to be cleaned up. This will delete the contents of folder silently.

rd Errors

This will delete the folder Errors.

md Errors

This will create a new folder names Errors. I know it is redundant, but why not start with a clean slate.

FOR %%A IN (*.SQL) DO ( sqlcmd -S MANU -d DATABASE1 -U username -P password -i "%%A" -o "Errors\%%AError_DB1.txt" -I )

Using a FOR loop we select each file name in sequential order as per the name of the file which ends with .sql extension in the current folder and pass the file name to execute with Server Name, Username, Password and the database name using SQLCMD.

%%A

here denotes the name of the current file name in the loop.

–o

outputs any errors/messages from the script to a file in the errors folder followed by the Database name.

Adding the database name can help you differentiate the error files when you add multiple FOR Loops for multiple databases. You can have n number of loops to n number of databases as in the example. I have 6 Databases that updates in one go.

Ready, Set, Run…

Now all you have to do is double click the batch file in the respective folders to execute all the scripts.

On executing the batch file, a new folder called Errors will be created which generated output from each script that was executed against the Server.

It would contain something like these.

If you see there are 2 files for CreateScript – 1.sql. Each error file pertains to different database.

The only caveat with this method is if there are no errors, it will still create the Error File. You can use the size of the file to get around this.

https://msdn.microsoft.com/en-us/library/ms162773.aspx is the command line reference to SQLCMD if you want to add more to this.