Cross Database SQL Scripts

Question

I'm working on a project where we talk directly from one database to another. We have a master database where the client updates their website and then changes are pushed from one database to the other through numerous SQL scripts. The master database works as a preview site allowing them to see changes before pushing them to live.

At the moment we code the slave database name directly into SQL queries.

e.g. INSERT INTO [slave-database].dbo.TableName

This is an absolute pain however when branching out the development where we'll have multiple sets of the two databases. Any stored procs relevant to the development going on are updated to point at the slave database for this particular branch e.g. [branch5-slave-database].

This is not ideal since changes have to then be manually tweaked when pushing into the trunk (changing out the hardcoded database names).

Is there a better way to do this? The only solution I can see at the moment would be to create a SQL Server instance for each branch which would mean the database names can always stay the same across all branches. This would be an absolute pain though and would have quite an overhead. Is it possible to set a constant for the database name within SQL Server? Allowing us to just update a single value on the branch databases. Dynamic SQL is out of the question.

Answer 1

Take a look at using the undocumented system stored procedure sp_MSforeachdb

sp_MSforeachdb

It allows you to process code against every database within the current instance. You could explicitly exclude system database should you with.

Answer 2

Some DBMS dialects permit databases to be opened with a database alias at connect time. This gives you some of the same flexibilities that tables aliases or column aliases do in other parts of SQL.

In this case, you could issue a connect with the database alias of "slave" no matter what the actual connect string is. Then you wouldn't have to code actual slave database names into queries. You would, however, have to manage opening and closing various slaves in succession so as to avoid a collision in the name "slave".

Another alternative is to use table links, if your dialect supports them. You will face a similar problem in avoiding collisions.

Answer 3

You can use dynamic Sql to change the name of the slave database. For example:

declare @query nvarchar(4000)
set @query = 'INSERT INTO [' + @slavedbname + '].dbo.TableName ...'
exec sp_executesql @query

You can store the name of the slave DB in a table and retrieve it from there:

select @slavedbname = value from Settings where name = 'SlaveDbName'

Or you could have a convention where it is equal to the current database plus _slave:

select @slavedbname = db_name() + '_slave'