Deploying database changes to multiple databases Part 1: Cursors

As a DBA, no matter where you work, there will come a time that you will need to deploy changes to multiple databases at a time. If you are lucky, it will be the same change, multiple times.

For me, I work for a software company where we host our clients data in separate databases. So when there is a code change that affects the database, these changes need to be propagated to multiple databases. For some of you reading this, you may have the time and patience to go through and run these scripts against each and every database. For the rest of us, I have a couple simple ways to push out changes that will hopefully be helpful to you, like they were me.

One way is with the old reliable cursor. If you’re not familiar with how to write a cursor for iterating through multiple databases, or at all, just follow along.

In this example, we host databases for car dealerships, and we want to add a column called Color to the Cars table in every one of our client databases. We have a table, clients, in our Dealership database, that lists all of our clients and the name of their database. First thing we want to do is to write our query for adding our column to the table. Just to keep it simple, we will make color a free text, or nvarchar column for this example.

So we come up with this script:

ALTER TABLE CARS ADD [Color] NVARCHAR(50) NOT NULL

Now that we know what we want to do to each database, we need to come up with a query to pull that list of databases that we need to update. For our example, we will use the following SELECT statement:

SELECT DBName FROM Clients

Since we will have a “USE” statement in our script to switch from database to database, we will use a string variable that we will set to our script that starts with the use statement and execute that. At the beginning of the script, we will declare our variable that we will use for our database name, sql statement, and the variable for the cursor itself. Here is our cursor for our example:

DECLARE DBName VARCHAR(50)
DECLARE SQL VARCHAR(250)

DECLARE dbnames CURSOR FOR
SELECT DBName FROM Clients

OPEN dbnames
FETCH NEXT FROM dbnames INTO @DBName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @SQL = ‘USE ‘ + @DBName + ‘
ALTER TABLE CARS ADD [Color] NVARCHAR(50) NOT NULL’

PRINT(@SQL)
EXEC(@SQL)

FETCH NEXT FROM dbnames INTO @DBName

END

CLOSE dbnames
DEALLOCATE dbnames

This has been an easy tool for me to deploy database changes to multiple client databases. The nice thing is for the cursor itself, you can keep reusing the cursor itself just by changing the alteration script inside the cursor to do whatever task you want to do.

This handy tool has saved me a lot of time in my current position, but the more complicated of a change you are looking to make, the more temperamental this style can be. For instance, if you want to create a function, you can not do it in a cursor because you can’t start a “CREATE FUNCTION” script with a “USE” command. It has to start with “CREATE FUNCTION”. This can also be temperamental when you are trying to create a table and load data, because these scripts tend to get a little more fussy about running in a cursor like our example. To make these types of changes, we need to explore other options for running these scripts like SSIS and Powershell.

Tomorrow, I will bring you Part 2. We will go over how to do the same change in SSIS using variables in conjunction with connection string expressions.