Hi there.
I realise that PowerShell is the next logical expansion which combines the utility of batch/command line interfaces with the power of managed code, but here’s a short entry on using loops in traditional batch scripts (just for fun).
On a recent project we wanted to execute SQL scripts sequentially, but we also wanted to output information so we could follow what was happening when the script was executed. This was basically a poor man’s equivalent of the powerful Visual Studio: Database Edition (data dude) deployment facility.
We had scripts in a “traditional” database project in Visual Studio (in a database project) so that Table creation scripts were in a subfolder, likewise for Stored Procedures, Relationships, et al.
The script would create the database, users, roles and so forth in the order intended. However to properly output information we wanted, we had to loop through the contents of each folder and execute the scripts. We had a fair bit of difficulty in figuring out how to execute multiple commands within the loop. The key was to use parenthesis to encapsulate commands (one command per line) as you can see in the following sample (note the use of other variables for Server name etc):
cd "Tables"
echo *********** Create Tables ***********
for %%f in (*.sql) do (
echo Executing ‘%%f’
sqlcmd -S %1 -E -d %2 -b -i "%%f" -v Db=%2
)
cd..
This was a very easy way for us to script out individual SQL files for each object (subsequently maintained under version control) and do single click deployments.
There are many better ways to achieve this result, but if you are short on time or resources, nothing beats a plain old batch script! This article helped immensely in finding this solution.
/R
