Using DB_NAME with MSSQL

I’ll admit, I haven’t used the DB_NAME very often. I pretty much use it in a single scenario. With all of my projects I use Continuous Deployment or Continuous Integration, whatever your choice of word is. When doing CI I have automatic deployment of any database scripts that need to run as I deploy to each environment: local, development, production, etc.

On occasion, a script needs to apply a custom value for each environment like a user ID. To accomplish I use DB_NAME to perform a case statement as follows.

Leveraging DB_NAME to apply custom logic

First I start by storing the name in a variable. Then I (typically) perform an update statement applying a case statement to specify the unique value per environment. You will need to know the database name of each environment:

[code]
DECLARE @dbName nvarchar(128)

SELECT @dbName = DB_NAME()

UPDATE MyTable SET
ValueField =
CASE
WHEN ‘LocalDatabase’ THEN 1
WHEN ‘DevelopmentDatabase’ THEN 2
WHEN ‘ProductionDatabase’ THEN 3
END
[/code]

In the example above, the table, field, database names, and the value will need to be updated to match your requirements.