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.