They created the database names with the environment suffix to make sure that applications under test don't accidently point to production. It's a real PITA for DB folks, especially during the promotion of code, but it's a REAL lifesaver if something goes wrong... goes wrong... goes wrong... (whack!) goes wrong.

I say it's a "real PITA" but it actually isn't. You just need to do a little more work as a DBA but you do NOT want to "re-touch" any code that has already been tested because YOU will be at fault if anything goes haywire.

How do you get around that?

1. Enforce a 2 part naming convention for all SQL Objects in ALL code... frontend or backend. Be a bitch about it, too! It will eventually make everyone's life a whole lot easier.

2. Setup SYNONYMs for each environment.

Here's an example... this sets up 4 databases, 2 "Dev" and 2 "QA" that start with my initials in an attempt to avoid collisions on your test box. I don't drop the databases at the end. When you're done, you can drop all 4 easily enough using the {f7} key (follow your nose when you get there) because they'll all be grouped together by name.

The rest is in the comments in the code that follows. It's a full demonstration that runs womb-to-tomb. Just make sure you read the comments so that you understand how easy this is. The only thing this type of thing won't help you with is if you have identically named objects in the two databases. If that's the problem, post back because I have a trick even for that using SYNONYMs.

USE tempdb; --Just a safe place to start

GO

--====================================================================

-- Create the "Dev" environment.

-- This is NOT a part of the solution. We're just setting up

-- the simulated environment here.

--====================================================================

--===== Create the databases for the Dev enfironment

CREATE DATABASE JbmMain_Dev;

CREATE DATABASE JbmDB01_Dev;

GO

--===== Create a View on the "JbmDB01" database

USE JbmDB01_Dev;

GO

CREATE VIEW dbo.SomeView AS SELECT ThisDBName = DB_NAME();

GO

--====================================================================

-- Create the "QA" environment.

-- Normally, this would be on a different server but this will do.

-- This is NOT a part of the solution. We're just setting up

-- the simulated environment here.

--====================================================================

--===== Create the databases for the Dev enfironment

CREATE DATABASE JbmMain_QA;

CREATE DATABASE JbmDB01_QA;

GO

--===== Create a View on the "JbmDB01" database

USE JbmDB01_QA;

GO

CREATE VIEW dbo.SomeView AS SELECT ThisDBName = DB_NAME();

GO

--====================================================================

-- Now, let's create a synonym on the "JbmMain" database of each

-- of the two environments that will allow us to look at the

-- correct database

-- This is a major part of the solution.

--====================================================================

--===== Setup the synonym in the "Dev" environment using a 2 part

-- naming convention.

USE JbmMain_Dev;

CREATE SYNONYM dbo.SomeView FOR JbmDB01_Dev.dbo.SomeView;

GO

--===== Setup the synonym in the "QA" environment using a 2 part

-- naming convention.

USE JbmMain_QA;

CREATE SYNONYM dbo.SomeView FOR JbmDB01_QA.dbo.SomeView;

GO

--====================================================================

-- Let's now pretend that you're promoting code to both

-- environments. The key here is that we don't have to change

-- the code just because we change environments.

--====================================================================

--===== Create a stored proc on the "JbmMain" database that will

-- select from the dbo.SomeView on the "JbmDB01" database.

-- This one is for the "Dev" environment.

USE JbmMain_Dev;

GO

CREATE PROCEDURE dbo.GetNameFromOtherDB AS

SELECT * FROM dbo.SomeView; --This is actually a synonym.

GO

--===== Create a stored proc on the "JbmMain" database that will

-- select from the dbo.SomeView on the "JbmDB01" database.

-- This one is for the "QA" environment. Note that is identical

-- to the one in the "Dev" environment. The difference is that

-- the "dbo.SomeView" synonym is pointing to the other database

-- in the "QA" environment.

USE JbmMain_QA;

GO

CREATE PROCEDURE dbo.GetNameFromOtherDB AS

SELECT * FROM dbo.SomeView; --This is actually a synonym.

GO

--====================================================================

-- Let's see how they run. Let's simulate an app calling the

-- stored procedure from each environment.

--====================================================================

--===== First, we'll execute the proc in the "Dev" environment.

USE JbmMain_Dev; --App wouldn't need this because of connection string

EXEC dbo.GetNameFromOtherDB;

--===== Now, we'll execute the proc in the "QA" environment.

-- Notice that we even EXEC the proc in exactly the same way.

USE JbmMain_QA; --App wouldn't need this because of connection string

EXEC dbo.GetNameFromOtherDB;