Querying multiple SQL Server databases

Sometimes I’d like to find a database containing data that could be used for testing purposes. What to do then? Either manually query databases until we find the one, or automatically query all at once. Querying all is not straightforward and this post contains my favourite ways.

sp_MSForEachDb

Undocumented, not supported, containing the severe bug, the system stored procedure is still the easiest way of querying multiple SQL Server databases.

The procedure is fairly easy to run, doesn’t require any additional actions to install it, as it exists by default in the master database. The bug is severe but not for anyone. As far as I know, it sometimes skips some databases without notification. So you may think that all were queried (or, worse, updated) but actually they weren’t. This is a showstopper for professional use, as you can’t rely on it. But for simple querying for data that might be useful, sometimes one can take the risk (which is not very high – I personally haven’ experienced it yet).

How to use it?

The simplest way is just to execute the procedure:

exec sp_MSforeachdb @command1 ='select "?", count(*) as TotalObjects from [?].dbo.sysobjects'  
exec sp_MSforeachdb @command1 ='use [?];exec sp_spaceused '  

Notice that „?” symbolizes the database name. The first query from above selects the database names and number of the objects inside each DB. Results from the two queries are presented here:

We can play with the „?” mark and narrow the set in which the procedure will operate. The one above queries databases which names starts from „PREFIX”:

EXEC sp_MSForEachDb @command1 =
'
IF ''?'' like (''PREFIX%'')
BEGIN
USE [?]
select ''?'', qc_version from qbs_config
END
'

But of course, we can use any conditional expression:

--to skip system databases
'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN
--to use databases with specific IDs only
IF DB_ID(''?'') > 10 BEGIN

This approach though has at least two weak points. It’s ok for quick queries across the databases, but it:

• returns nothing if an exception occurs
• returns multiple resultsets instead of one with multiple rows

Fortunately, we can do something with it. Here is the simplest approach, that I use:

EXEC sp_MSForEachDb @command1 =
N'
BEGIN TRY
      use [?];
      exec sp_spaceused;
    END TRY
    BEGIN CATCH
      PRINT ''? failed.'';
    END CATCH';

This simple Try/Catch construct make our procedure running even if any failure occurred.

What about one resultset? We have to create a temporary table for results and redirect output from the procedure to it. The final version looks like this then:

declare @results table (
database_name nvarchar(128),
qc_version nvarchar(18)
);
insert @results EXEC sp_MSForEachDb @command1 =
N'
BEGIN TRY
    IF ''?'' like (''cv%'')
    BEGIN
	use [?];
	select ''?'' as database_name, qc_version from qbs_config;
    END
    END TRY
    BEGIN CATCH
      PRINT ''? failed.'';
    END CATCH';

select * from @results

• Lines 1-4: declare temporary table where all results will be stored
• Line 5: insert keyword added – it will append results from sp_MSForEachDb to the @results table
• Line 7: part of Try/Catch construct
• Line 8: filtering by database name

Now the results look like this:

Much easier to examine them, at least for me. And that’s it for simple querying. I’ve never needed anything more complex for my daily tasks. But:

1. my tasks are rather simple
2. I don’t do critical mass updates

As so, sp_MSForEachDb is more than enough for me. More demanding users will look for something more reliable. They probably know the answer already, but I’m adding a short summary especially for them:

SQL Server First Responder Kit

It’s a powerful toolbox containing many useful scripts for anyone working with databases on a slightly higher level than „just query”. But we’re interested in only a part of it, called sp_ineachdb.sql

It (hopefully) doesn’t have MS version flaws. It’s open-source, with well-known code, so I think it’s much more reliable than its predecessor. Its interface is very similar to the MS version. For our purposes, it’s enough to present such an example. The changes are rather cosmetic:

declare @results table ( database_name nvarchar(128), qc_version nvarchar(18));
insert @results EXEC sp_ineachdb @command =
N'BEGIN TRY IF DB_NAME() like (''cv%'') 
BEGIN select db = DB_NAME(), qc_version from qbs_config; 
END 
END TRY 
BEGIN CATCH 
PRINT ''? failed.''; 
END CATCH';
select * from @results

Major problem is that it isn’t installed on MS SQL Server by default and has to be added manually. Sometimes you don’t have permission to do this. But if you have, then installation can be done as simple as:

1. Open SQL Server Management Studio and connect to the desired server.
2. Copy sp_ineachdb.sql text from the link I posted above.
3. Run the code, it will create or alter the procedure.
4. The procedure is ready to be used.

I think that’s it. If you don’t know what such multiple querying can be used for, think about this simple scenario: you need to find the database which is in a certain version and contains a set of invoices with certain IDs. In general, in my current work, I use it anytime I need to find a certain set of data in the database with a specific version, but I think that anyone will find their own favourite applications.