Forum Discussion

genesix1970's avatar
genesix1970
Copper Contributor
Jan 14, 2024

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

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

 

 

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.

    • genesix1970's avatar
      genesix1970
      Copper Contributor
      Jan 15, 2024

      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

      • LainRobertson's avatar
        LainRobertson
        Silver Contributor
        Jan 15, 2024

        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

         

        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

Resources