Every now and again it can be very helpful to know what the current database context is when you are writing T-SQL code. It’s actually pretty simple. Use DB_Name() and/or DB_ID().

You can use them without a parameter to return the name or id of the current database context.

USE master
GO
SELECT DB_NAME() name, DB_ID() database_id
UNION ALL
SELECT name, database_id FROM sys.databases WHERE name = 'master'
GO
USE Test
GO
SELECT DB_NAME() name, DB_ID() database_id
UNION ALL
SELECT name, database_id FROM sys.databases WHERE name = 'Test'
GO

Or with a parameter to return the same information about a specific database.

SELECT DB_NAME(1) name, DB_ID('master') database_id
UNION ALL
SELECT DB_NAME(5) name, DB_ID('Test') database_id

I actually use them this way on a somewhat regular basis when I don’t want to have to tie back into sys.databases to just get the database name. And also in the WHERE clause when I want to restrict based on a database name.

SELECT DB_NAME(database_id), * FROM sys.master_files
WHERE database_id IN (DB_ID('master'), DB_ID('Test'))

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: microsoft sql server, T-SQL