Recently I went through requirement of running SQL scripts on multiple databases on different servers. As the list of databases was different from script to script I decided to create universal solution, which will allow to easily prepare for deployment and can be simply reused at any time.

As a platform to execute scripts I went with PowerShell as that provides flexibility in case additional functionality will be required.

Whole solution contains 3 files:

SqlExecutionInflow.csv – file contains list of databases and servers where particular database is located. It is simple CSV file with 2 columns

SqlExecQuery.sql – contains SQL script which will be executed against all databases listed in SqlExecutionInflow.csv

SqlExec.ps1 – main script which load SqlExecInflow.csv and executes query from SqlExecQuery.sql

All files have to be placed in same folder. As a result script will create transcript file with output from all executed commands.

Use encrypted password in script

Remember that password has to be encrypted on the account which will be running the script. So, in case you plat to use Task Scheduler to run PowerShell script, start PowerShell windows as particular user and then create file with encrypted password.