SQL Server – Union of Results to Include Database Name

sql server

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.

Best Answer

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.

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:

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

  1. Create an instance table on one of your instances.
  2. Create a table to store the data you want to collect (probably the same instance).
  3. In an SSIS package create a SQL Task that queries the instance table and stores the results in an object variable.
  4. Create a for each loop that loops through the variable.
  5. In the for each loop change a variable to be equal to the current instance name from the object variable.
  6. Create a connection manager that uses an expression to change the server name property to the contents of the instance variable.
  7. In the for each loop create a data flow
    1. The data flow source uses the connection manager with the expression.
    2. Put the query that collects your data in the source.
    3. Set the destination to the table you created in step 2.
Related Question