{"id":981,"date":"2021-07-19T11:58:01","date_gmt":"2021-07-19T09:58:01","guid":{"rendered":"https:\/\/fileidea.com\/?p=981"},"modified":"2021-07-19T14:32:27","modified_gmt":"2021-07-19T12:32:27","slug":"run-sql-queries-on-multiple-databases","status":"publish","type":"post","link":"https:\/\/fileidea.com\/2021\/07\/19\/run-sql-queries-on-multiple-databases\/","title":{"rendered":"Run SQL queries on multiple databases"},"content":{"rendered":"\n
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.<\/p>\n\n\n\n
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<\/strong>. Keep in mind that it must have the file extension ps1 since that is what makes it a powershell file. Let us write this:<\/p>\n\n\n
So what the script does is that it goes through the files inside the folder C:\\Users\\Administrator\\Desktop\\script\\testing<\/strong> and it will open up each file that ends with .sql<\/strong> and has the file name RUN<\/strong> 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<\/strong>.<\/p>\n\n\n\n
So this means we should create a SQL file inside the testing folder and in it, call it something like RUN-1.sql<\/strong> with your SQL code. Put anything it such as your INSERT or UPDATE or whatever. I am going to have a simple hello script:<\/p>\n\n\n
\nprint 'hello'\n<\/pre><\/div>\n\n\n
Assuming you have the MyFirstDatabase, MySecondDatabase and MyThirdDatabase<\/strong> databases and the file RUN-1.sql<\/strong> file inside the C:\\Users\\Administrator\\Desktop\\script\\testing<\/strong> 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<\/strong> (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!<\/p>\n\n\n\n<\/figure>\n\n\n\n
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.<\/p>\n\n\n\n
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 <\/strong>command each time.<\/p>\n\n\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 … <\/p>\n
![\"\"](\"https:\/\/fileidea.com\/wp-content\/uploads\/2021\/07\/image-7.png\")
<\/figure>\n\n\n\n