Execute same SQL Server script in multiple databases

In one of my projects, same database schema is used in multiple databases for some practical reasons. Deployment is a challenging job in this case. We have 15 databases with the same schema. To overcome this issue, I have created a batch file which does job for us.

cls

rem Usage : DatabaseScripts <> <> <> <>
echo Welcome to Deployment Script
SET SOURCEFOLDER=%1
SET UserName=%2
SET Password=%3
SET ServerName=%4

IF /I "%SOURCEFOLDER%" == "" (
   ECHO "PATH TO SOURCE FOLDER NOT SPECIFIED".
   GOTO QUIT
)

IF /I "%UserName%" == "" (
   ECHO "User Name NOT SPECIFIED".
   GOTO QUIT
)

IF /I "%Password%" == "" (
   ECHO "Password NOT SPECIFIED".
   GOTO QUIT
)
IF /I "%ServerName%" == "" (
  ECHO "Server Name NOT SPECIFIED".
  GOTO QUIT
)

IF NOT EXIST "%SOURCEFOLDER%" (
   ECHO SOURCE FOLDER "%SOURCEFOLDER%" DOES NOT EXIST.
   GOTO QUIT
)

REM pass database names in for loop
FOR %%D IN (db1 db2 db3 db4) DO (
   sqlcmd -S %ServerName% -U %UserName% -P %Password% -d %%D -i %SOURCEFOLDER%\Deployment.sql -o %SOURCEFOLDER%\deploy_log_%%D.txt
)
ECHO "Deployed the script successfully"
pause

:QUIT
 
The above batch file accepts below parameters
 

1. SOURCEFOLDER: Folder location where deployment script is located. It assumes that file name is Deployment.sql.
2. UserName: UserName for the database
3. Password: Password for the database
4. ServerName: Database server Name

 After execution of the scripts, it will create log file for each database. Log files will be created in directory where the database deployment script is located.

Sqlcmd is a command line tool which can be used to deploy scripts in by reading from file.