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

1

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?

Thank you.

result
result851×306 58.5 KB

2 

EXEC sp_MSForEachDB
N' 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]
'
3

thank you but this is not what I am looking for

4

I am receiving an error

Invalid column name 'DBVERSION'.

5

Hmm, since you checked for the existence of the column before exec'ing the query, that doesn't seem possible ... unless the id running the query doesn't have access to that table in that db.

Is it possible that multiple versions of the table (or view) "Application_Global" exist? (Obviously under different schemas.) That could also cause the error you're seeing.

It's' best to avoid info_schema views in SQL Server, you should use sys.columns instead:


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

That too failed to work.

Thanks anyway Scott.

7

Hi Scott, I tweaked the script that you have provided and it worked. Thank you very much for your input.

EXEC sp_MSForEachDB
N' USE [?]; IF EXISTS (SELECT * FROM sys.columns
WHERE OBJECT_NAME(object_id) = ''Application_Global'' AND name = ''COMPANY_NAME'')
SELECT ''?'' AS DB_NAME, [COMPANY_NAME],[DBVERSION]
FROM [Application_Global]'