Sometimes you have multiple databases that you want to run SQL queries on. It can be time consuming to go through each one after one. The method that works the easiest for me to accomplish this is to do it through Powershell. You can also do it by creating an SQL function where you pass the database name and run the script through the script itself, but I found that Powershell works just as good if not even better, specially if you have long queries and multiple sql script files. I should say that, I am running these commands against a SQL Azure but there this should work in other situations as well.
First of all, open up Notepad or something to write the Powershell code, and name your powershell file to whatever and wherever you want, I’ll go with the name run.ps1. Keep in mind that it must have the file extension ps1 since that is what makes it a powershell file. Let us write this:
Get-ChildItem "C:\Users\Administrator\Desktop\script\testing" -Filter RUN*.sql | `
Foreach-Object{
Write-Host 'Executing' $_.FullName
sqlcmd -U myservices@azuresqlserver.database.windows.net -P mypassword -S azuresqlserver.database.windows.net -d MyFirstDatabase -i $_.FullName
sqlcmd -U myservices@azuresqlserver.database.windows.net -P mypassword -S azuresqlserver.database.windows.net -d MySecondDatabase -i $_.FullName
sqlcmd -U myservices@azuresqlserver.database.windows.net -P mypassword -S azuresqlserver.database.windows.net -d MyThirdDatabase -i $_.FullName
}
So what the script does is that it goes through the files inside the folder C:\Users\Administrator\Desktop\script\testing and it will open up each file that ends with .sql and has the file name RUN in it. The script execute the .sql from these files (we will create these soon) and then execute that sql code into our databases MyFirstDatabase, MySecondDatabase and MyThirdDatabase.
So this means we should create a SQL file inside the testing folder and in it, call it something like RUN-1.sql with your SQL code. Put anything it such as your INSERT or UPDATE or whatever. I am going to have a simple hello script:
print 'hello'
Assuming you have the MyFirstDatabase, MySecondDatabase and MyThirdDatabase databases and the file RUN-1.sql file inside the C:\Users\Administrator\Desktop\script\testing you can now run your powershell file, note that it can be saved anywhere, as long as the sql files are at the correct destination. Open up Powershell with administrator rights (right click and run as administrator) or Windows Powershell IDE and browse to the location of your powershell file, and enter the run.ps1 (which I named my powershell file) and click enter. Powershell will now run the script and print our hello three times which means we’ve just run some SQL code through powershell successfully!
You’d obviously have some actual SQL that does something, but for now, we can see it is working as we expected. And you can create multiple sql scripts with various queries if you want to structure things and they will be included if you name them RUN in the file name.
Now the script is still little bit clutterly, so why not put the database names into an array so we can just update that instead of the whole sqlcmd command each time.
$databases = @('MyFirstDatabase','MySecondDatabase','MyThirdDatabase')
For ($i=0; $i -lt $databases.Length; $i++) {
$databases[$i]
Get-ChildItem "C:\Users\Administrator\Desktop\script\testing" -Filter RUN*.sql | `
Foreach-Object{
Write-Host 'Executing' $_.FullName
sqlcmd -U myservices@azuresqlserver.database.windows.net -P mypassword -S azuresqlserver.database.windows.net -d$databases[$i] -i $_.FullName
}
}
We pass the database name into the sqlcmd -d (database) command and thus we can loop through the list easily and run SQL queries on multiple databases.