Forum Discussion
How to display the database name in the result of a query?
Hi, Eugene.
You can use the DB_NAME() function to fetch the name of the current database as an additional column.
Example
SELECT
DB_NAME() AS [dbName];
Cheers,
Lain
I've actually used that DB_NAME function and it only works with single database. The script that I used above works with 15 databases in one single SQL instance. I was able to query the DBVERSION and COMPANY_NAME column for each database correctly but I could not display the database name for each row. Someone suggested this work around;
EXEC sp_MSForEachDB
IF EXISTS (SELECT * FROM [?].INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ''Application_Global'' AND COLUMN_NAME = ''DBVERSION'')
SELECT ''?'' AS db_name, [Application_Global].[DBVERSION], [COMPANY_NAME]
FROM [?]..[Application_Global]
EXEC sp_MSForEachDB
N' USE [?]; IF EXISTS (SELECT * FROM sys.columns
WHERE OBJECT_NAME(object_id) = ''Application_Global'' AND name = ''DBVERSION'')
SELECT ''?'' AS db_name, [Application_Global]. [DBVERSION], [COMPANY_NAME]
FROM [Application_Global]
Both scripts return an Invalid column name 'DBVERSION' result
I don't use sp_msforeachdb, which is apparently undocumented and unsupported, but at least anecdotally from the following simple test, I can't see any issue with DB_NAME() not returning the correct database name. Though, of course - since you'd have to do this or else the database context is indeed not switching (in which case you'd expect DB_NAME() to return the same database name every time), I am using a USE statement to change database context first.
Example
EXEC sp_MSforeachdb ' USE ?; SELECT @@VERSION AS [serverVersion] , DB_NAME() AS [dbName]; ' GOOutput
I can't comment on your reference to "DBVERSION" as I've never heard of a function with that name. Perhaps someone else has.
Cheers,
Lain
- By the way Lain, I really do appreciate the input that you shared with me.
Given I don't have the table you're trying to work with, I can only suggest trying (i.e. all I can do is guess) the following, through it's not particularly different to your second example from above.
EXEC sp_MSForEachDB ' USE [?]; IF EXISTS ( SELECT * FROM [?].INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ''Application_Global'' AND COLUMN_NAME = ''DBVERSION'' ) SELECT DB_NAME() AS db_name , [Application_Global].[DBVERSION] , [COMPANY_NAME] FROM [?]..[Application_Global] ' GOUsing my own existent table and column references, this query delivers what you're seeking in your environment.
Cheers,
Lain
- DBVERSION is not a function, it's a column name of a table that I am working on right now. DBVERSION is the information that I am trying to extract in my query together with the COMPANY_NAME information and database name. Refer to my attachment.