This has come up a few times in work so this will serve as a reminder to myself when I need to do this again.

Problem: Legacy database naming conventions

All our sql changes in work need to be made using rerunnable scripts that a database person can run. Unfortunately we sometimes run into a problem with the legacy naming conventions used when creating these database. One in particular which drives me nuts is that all our dev databases are named {databasename}_dev. This in spite of the fact that our dev database exist on a dedicated development database server.

Where this becomes an issue is when we need to write scripts that pull data from one database into another.

Example Cross Database Script

In this script which we run on the database2 we are pulling in data from a second named database.

-- This script to be run on database2 to pull in data from database1
INSERT INTO database2.dbo.tablename (cols)
SELECT cols
FROM database1.dbo.tablename

Written like this it works fine on production, but on development it blows up as it needs to be written like this.

-- This script to be run on database2 to pull in data from database1
INSERT INTO database2_dev.dbo.tablename (cols)
SELECT cols
FROM database1_dev.dbo.tablename

Note the "_dev" in the database names. While we can omit the database2 and database2_dev names from the scripts as we are running them on the target database, we cannot omit the database1 and database1_dev names.

Check the database exists by its Id

The solution we came up with is to check if the database exists by looking for its id for one of the two names. We can assume that if one if found the other is not valid and vice versa. Then its a simple job to do an IF ELSE block.

-- Switch between database names depending on server
IF db_id('database2_dev') is not null
BEGIN
    INSERT INTO database2_dev.dbo.tablename (cols)
    SELECT cols
    FROM database1_dev.dbo.tablename
END
ELSE
BEGIN
    INSERT INTO database2.dbo.tablename (cols)
    SELECT cols
    FROM database1.dbo.tablename
END