I was recently working on an issue in the production environment. We have an application which is a combination of an SQL database and a .net application. So, most of the fixes we do will either be in the form of code or just a SQL script that will fix the metadata in the database. For the issue that we identified recently, we had to alter a SQL view to fix the issue. The tricky part here is that I have to execute the script on around 700 databases in a production environment. If it was a single database it shouldn't be a challenging task.
I initially thought of having the SQL view in a cursor script to loop through all the databases and execute it. But unfortunately, it is not possible to run DDL statements like that using a cursor script. Scripts with GO command cannot be used inside cursor. After researching a bit and looking for alternative solutions, I finalized that the Powershell script should be an easy way to execute the script looping through all the databases.
First I had to make sure if it was possible for me to use PowerShell script for this task as I was not sure what type of restrictions they would have implemented in the production environment. If there was no permission to run scripts from Powershell, it would have been a tough task. But luckily PowerShell had permissions to execute SQL commands. I wrote the below script to complete the task. I'm going to split the script into three parts and traverse you through the script.
In order to open a connection, we will need the following parameter values:
These are the values that are required to prepare a connection string. You can give these values based on your server details. Invocation path refers to the path from which the PowerShell script is executed. You can also give a direct path to your SQL script if in your case, the script is going to be in a different location.
In this part of the code, we are going to establish a connection to the database with the connection string that we created in the previous code block. I'm using a try catch block to handle exceptions in the code. So, we create a dataSet and write a query to get all the databases from the server.
SELECT [name] FROM [$dbName].dbo.sysdatabases WHERE dbid > 4
In the above code, we can also use the master database directly instead to get all the databases. We have to give dbid as greater than 4 because the first 4 databases are always system databases. As a next step, we initiate a data adapter and pass the query to it. We then fill the values we get to the dataSet. At the end of this code, the dataSet will have the list of all the databases from the server.
This is the part where we actually execute the script. In this part of the code, we use foreach command to loop through the dataSet that we created and run the SQL script against each database one by one. Just to see which database is currently being performed, I have used an echo statement before and after the query execution.
invoke-expression "SQLCMD /S '$dbHostInstance' /U '$dbUsername' /P '$dbUserPassword' /l 60 /d '$($row[0])' /i '$ScriptPath' /v DB = '''$($row[0])'''" -ErrorVariable err2 -ErrorAction Stop
This part of the code then executes the provided SQL script on all the databases one by one. This is a little time-consuming process and if you have more databases in the server this can take a while. But in the log, you will be able to see which database is currently being processed.
That's all and it is as simple as that. If you like my content, upvote, follow, and share. You can also share your thoughts in the comments section.
Carbon was used to create Code Screenshots
If you find this article interesting, please vote, share and follow! Also, please share your thoughts in the comments section.