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.