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.
And here are example files and script itself…