What is the current database context?
3December 21, 2015 by Kenneth Fisher
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'))
[…] Kenneth Fisher shows how to use DB_NAME() and DB_ID(): […]
Why not just:
select db_name(db_id())
go
Why have the extra function call? That’s functionally equivalent of db_name().