Run query on each DB and display DB Name too

Question

using samples I found in this site, I succedeed to write the following query. It works well, however, I would like to get the name of each database being queried be reported in the results, next to those 2 columns. All my attempts have failed. Any help, please.

DECLARE @sql varchar(max);

SELECT @sql = Coalesce(@sql + ' UNION ALL ', '') + 'SELECT column1, column2 FROM ' + QuoteName(name) + '.dbo.T_MyTable'
FROM   sys.databases WHERE database_id > 4 AND state = 0
;

PRINT @sql
EXEC (@sql);

as an example, the following fails with an Invalid column name error

DECLARE @sql varchar(max);

SELECT @sql = Coalesce(@sql + ' UNION ALL ', '') + 'SELECT SoStoreNameTx, SoStoreNoTx, ' + quotename(name) + ' FROM ' + QuoteName(name) + '.dbo.T_SoStore'
FROM   sys.databases WHERE database_id > 4 AND state = 0
;

PRINT @sql
EXEC (@sql);

Did you try adding Quotename(name) to column list after column2? (before FROM keyword). It should be embraced with '' so you just select a string. — PacoDePaco
– PacoDePaco, Commented Sep 13, 2017 at 10:38
Thank you, I have and it failed. I edit my question to add the failing query — DeepDiver
– DeepDiver, Commented Sep 13, 2017 at 10:43
@DeepDiver look at the PRINT - you are selecting the database name without containing it in single quotes - The answer below shows you how you should do it. — Keith
– Keith, Commented Sep 13, 2017 at 10:45
See my answer. In your query SQL Server thinks that the quoted name is a name of the column — PacoDePaco
– PacoDePaco, Commented Sep 13, 2017 at 10:45

PacoDePaco · Accepted Answer · 2017-09-13 10:41:46Z

1

Try the code below

   DECLARE @sql varchar(max);

    SELECT @sql = Coalesce(@sql + ' UNION ALL ', '') + 'SELECT column1, column2, ''' + QuoteName(name)+  ''' as DBname FROM ' + QuoteName(name) + '.dbo.T_MyTable'
    FROM   sys.databases WHERE database_id > 4 AND state = 0
    ;

    PRINT @sql
    EXEC (@sql);

answered Sep 13, 2017 at 10:41

PacoDePaco

6991 gold badge5 silver badges16 bronze badges

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

1 Comment

DeepDiver Over a year ago

That works perfectly, really appreciated. It will also help me patch another similar query.

Collectives™ on Stack Overflow

Run query on each DB and display DB Name too

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related