5

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.

1
  • What's wrong with first take, it should give the result set which could be copied all at once?
    – Serg
    Jun 17 '16 at 21:20
12

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.
2
  • To run this on multiple servers, you could add details about using registered servers...
    – Hannah Vernon
    Jun 18 '16 at 1:10
  • @MaxVernon Honestly at that point I'd create an SSIS package that loops through each of the instances & collects the data rather than create a bunch of linked servers just for this one task. It's also more flexible in the long run. Jun 19 '16 at 15:34
2

I like to use a template and then perform a replace string then execute it, for example:

SET @SQL_SCRIPT= '
INSERT INTO ##DataTables
(DatabaseName, [Other Information])
SELECT
    ''{DATABASE_NAME}'' AS DatabaseName, ....'

Then before execution, do a replace:

SET @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{DATABASE_NAME}', @DBName)

Where @DBName is the database name of course.

1
  • Thanks for the help both. I went with the first answer - I had read something about not using the SP but just gave it as an example of something that gave me the results I was looking for just not in one result set.
    – user98149
    Jun 19 '16 at 7:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.