DB_NAME() in SQL Server when using [DBName].[SchemaName].[TblName] query syntax

Question

The following query returns the name of the current database:

USE [DB1]
SELECT DB_NAME() AS Current_DB
FROM [dbo].[TblName]

Result:

Current_DB
----------
DB1

I'm seeing very strange results when using DB_NAME() with queries that SELECT FROM a table in [DBName].[SchemaName].[TblName] notation:

T-SQL

USE [DB1]
SELECT DB_NAME() AS Current_DB
FROM [DB1].[dbo].TblName
UNION
SELECT DB_NAME() AS Current_DB
FROM [DB2].[dbo].TblName
UNION
SELECT DB_NAME() AS Current_DB
FROM [DB3].[dbo].TblName
UNION
SELECT DB_NAME() AS Current_DB
FROM [DB4].[dbo].TblName

Result:

Current_DB
----------
DB1

T-SQL

USE [DB1]
SELECT DB_NAME() AS Current_DB
FROM [DB1].[dbo].TblName
UNION ALL
SELECT DB_NAME() AS Current_DB
FROM [DB2].[dbo].TblName
UNION ALL 
SELECT DB_NAME() AS Current_DB
FROM [DB3].[dbo].TblName
UNION ALL
SELECT DB_NAME() AS Current_DB
FROM [DB4].[dbo].TblName

Result:

Current_DB
----------
DB1
DB1
DB1
...  (632,788 rows of DB1 !!!)

Even if I omit the USE [DB1] from T-SQL query, I get the same results - the database selected in the Query Target dropdown menu of SSMS is the one appearing in the results.

How can I get the correct DB_NAME() across multiple queries using [DBName].[SchemaName].[TblName] format?

As you said returns the name of the current database. Not the database of the table(s) in the FROM clause. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Feb 26, 2019 at 12:04
what you are trying to achieve? you already know the db name when you use [DB1].[dbo].TraceContactSource — PSK
– PSK, Commented Feb 26, 2019 at 12:08
You can't. The only way to get DB_NAME() to return the name you want is to do use [dbname] first. And if you can do that, you already have the name... — GuidoG
– GuidoG, Commented Feb 26, 2019 at 12:09

EzLo · Accepted Answer · 2019-02-26 11:59:25Z

4

DB_NAME() without parameter shows the database name of the currently connected session, not the underlying database of the object you are querying.

From the docs:

A. Returning the current database name. This example returns the name of the current database.

SELECT DB_NAME() AS [Current Database];

Otherwise what would you expect if you do a join between databases and call DB_NAME()?

SELECT
    DB_NAME() -- ??
FROM
    Database1.dbo.Table1 AS T
    CROSS JOIN Database2.dbo.Table2 AS N

You can switch connected databases with the USE <DatabaseName> statement.

answered Feb 26, 2019 at 11:59

EzLo

14.2k10 gold badges38 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Zohar Peled · Accepted Answer · 2019-02-26 12:00:55Z

1

The DB_NAME() function returns the name of the current database if the database_id argument is not specified.

current database does not mean the database where the table you are querying lives, but the database you are currently connected to.

The difference between the union and the union all queries is that union returns distinct values - and since you are selecting the same value in every query, a single value is returned.

answered Feb 26, 2019 at 12:00

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

3 Comments

Adam Over a year ago

Thanks. Any idea why the UNION ALL is returning 632,788 even though only 4 queries are UNION ALL'd ?

Zohar Peled Over a year ago

Yes - there are a total of 632,788 rows in all the tables combined. If you where to do (select count(*) from table1) + (select count(*) from table2) and so on that's the number you would get.

Adam Over a year ago

Thanks, that makes sense! :)

Thom A · Accepted Answer · 2019-02-26 12:12:37Z

0

DB_NAME() returns the name of the database you are currently connected to. A query can reference multiple databases in the FROM, so what would expect for a query like?

SELECT DB_NAME()
FROM DB1.dbo.YourTable YT
     JOIN DB2.dbo.OtherTable OT ON OT.ID = YT.ID;

For your query, you need to specify the name of the database in your query:

USE [DB1]
SELECT N'DB1' AS Current_DB
FROM [DB1].[dbo].TraceContactSource
UNION ALL
SELECT N'DB2' AS Current_DB
FROM [DB2].[dbo].TraceContactSource
UNION ALL 
SELECT N'DB3' AS Current_DB
FROM [DB3].[dbo].TraceContactSource
UNION ALL
SELECT N'DB4' AS Current_DB
FROM [DB4].[dbo].TraceContactSource

As per the comments, however, if you're actually after a list of (non-system) databases, perhaps this achieves your goal:

SELECT [name] AS Current_DB
FROM sys.databases
WHERE database_id > 4;

edited Feb 26, 2019 at 12:12

answered Feb 26, 2019 at 12:02

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

4 Comments

Zohar Peled Over a year ago

Unless adding records from the actual tables, the from clause is redundant - you can simply do SELECT N'DB1' As Current_Db UNION ALL SELECT N'DB2' UNION ALL SELECT N'DB3'...

Thom A Over a year ago

Infact, SELECT [name] AS Current_Db FROM sys.databases WHERE database_id > 4; might be better, depending on their goal, @ZoharPeled, depending on their goal.

Adam Over a year ago

I'm looking for a way to do this without hard-coding the DB name in the query - I am using sp_executesql and running this code across 100+ db's to create a comparison report. I have found a way to do this now... thanks anyway :)

Thom A Over a year ago

Sounds like what we had here was an XY problem, @Adam .

Collectives™ on Stack Overflow

DB_NAME() in SQL Server when using [DBName].[SchemaName].[TblName] query syntax

3 Answers 3

Comments

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related