How to display the database name in the result of a query?

Copper Contributor

sp_MSForEachDB
' BEGIN IF EXISTS (SELECT * FROM [?].INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ''Application_Global'' AND COLUMN_NAME = ''DBVERSION'')
BEGIN DECLARE @sql NVARCHAR(MAX); SET @sql = (''SELECT [Application_Global].[DBVERSION], [COMPANY_NAME] from [db]..[Application_Global]'')
SET @sql = REPLACe(@sql, ''[db]'', ''[?]''); EXEC sp_executesql @sql;
END; END';

 

 

result.jpg

The objective is to query all the available database in a single server and I want the database name to be included in the result per row. How can I accomplished that? Please help.

 

Thank you.

6 Replies

@genesix1970 

 

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

@genesix1970 

 

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];
'
GO

 

Output

LainRobertson_0-1705299725219.png

 

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

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.
By the way Lain, I really do appreciate the input that you shared with me.

@genesix1970 

 

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]
'
GO

 

Using my own existent table and column references, this query delivers what you're seeking in your environment.

 

Cheers,

Lain