Union of results of all database query to also include database name

I'm hoping someone can help. I have a number of servers each with multiple databases on it. All of the databases have a table I want to query with a very simple 'select count(*) from section'. I want to get the results plus the database name all collated together (i.e. not as separate query results that I can't copy out in one quick click). So results like:

**DB Name** **Number of Sections**

Name1  10

Name2 20

Name3 11

Name4 9

Name5 20

So far I've got two queries as below:

This query gives me a list of the results as one query altogether, however it doesn't give me the DB Name that the result relates to and I can't suss out how to add it.

DECLARE @sql varchar(max);

SELECT @sql = Coalesce(@sql + ' UNION ALL ', '') + 'SELECT count(*) as ''Number of Sections'' FROM ' + QuoteName(name) + '.dbo.section'
FROM   sys.databases
where database_id > 4
;

EXEC (@sql);

This query gives me the exact results I want but they are all separate queries so I'd have to go through and copy each one manually to collate the results.

DECLARE @command varchar(1000) 

SELECT @command = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN USE ? SELECT ''?'', COUNT(*) FROM section END' 

EXEC sp_MSforeachdb @command

If anyone can give me an idea on how I can get the output I want it would be much appreciated.

Just add the DBName to your first query:

DECLARE @sql varchar(max);

SELECT @sql = Coalesce(@sql + ' UNION ALL ', '') + 
        'SELECT '''+name+''' AS DBName, count(*) as ''Number of Sections'' FROM ' + QuoteName(name) + '.dbo.section' + CHAR(13)
FROM   sys.databases
where database_id > 4;

--PRINT @sql
EXEC (@sql);

Generally you want to avoid sp_msforeachdb. It has some well known bugs.

• https://sqlblog.org/2010/12/29/a-more-reliable-and-more-flexible-sp_msforeachdb

Edit: If you want to run this on multiple servers you could write a quick SSIS package that loops through an instance table and collects the data to a central location. I did a walkthrough on the following 3 posts:

• Using a fixed list and a for each loop that changes a connection manager with expressions.

• Using the package to store data to a central location.

• Touching up the package and expanding it to use an instance table to store the list of servers.

Combining all of that into this answer would be a bit long but here are the basics:

• Create an instance table on one of your instances.

• Create a table to store the data you want to collect (probably the same instance).

• In an SSIS package create a SQL Task that queries the instance table and stores the results in an object variable.

• Create a for each loop that loops through the variable.

• In the for each loop change a variable to be equal to the current instance name from the object variable.

• Create a connection manager that uses an expression to change the server name property to the contents of the instance variable.

• In the for each loop create a data flow

• The data flow source uses the connection manager with the expression.

• Put the query that collects your data in the source.

• Set the destination to the table you created in step 2.

StackExchange Database Administrators Q#141572, answer score: 12