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:
DECLARE @dbName nvarchar(128)
SELECT @dbName = DB_NAME()
UPDATE MyTable SET
WHEN ‘LocalDatabase’ THEN 1
WHEN ‘DevelopmentDatabase’ THEN 2
WHEN ‘ProductionDatabase’ THEN 3
In the example above, the table, field, database names, and the value will need to be updated to match your requirements.