Execute a Command in the Context of Each Database in SQL Server using sp_ineachdb

By: Aaron Bertrand   |   Comments (9)   |   Related: > TSQL

Problem

In a previous tip, I showed you how to create a more reliable and flexible sp_MSforeachdb. This replacement avoids the problems inherent in the native solution where it skips databases, fails against databases with problematic identifiers (like CREATE DATABASE [1db'-.foo];), and adds options (like only execute against system databases, databases in a certain recovery model, or databases matching a certain name pattern). The title of this tip sounds a lot like a duplicate, but it is not.

Recently, a question came up about why it doesn’t execute the command in the actual context of the database; the example was that SELECT DB_NAME() reflects the name of the calling database over and over again, rather than the database where the command was expected to run. Many people assume that it should issue a USE currentdb; command for you, but that’s not how it works, and so a function like DB_NAME() will only ever run in the context of the caller.

Solution

My replacement works like this because I wanted to exactly mimic the behavior of the native system procedure. It takes your command, loops through each database, replaces every instance of ? in the command with the current database name, and executes. The same symptom will happen if you run the following command:

USE some_database;
GO
EXEC dbo.sp_foreachdb N'SELECT COUNT(*) FROM sys.objects;';

You will get the same result every time. Why? Because it will count the objects in some_database every time. In order to make it look at the other databases, you have to include the replacement character (?):

USE some_database;
GO
EXEC dbo.sp_foreachdb N'SELECT COUNT(*) FROM ?.sys.objects;';

But it doesn’t have to be this way. We can also create a stored procedure that actually executes your command – without any replacement – in the context of each database, without having to add all the instances of ? yourself, or manually adding USE [?]; to the beginning of each command. Both of these can be troublesome if you are generating scripts or deriving them from a source and hoping to execute them without manipulation.

And so, I present a stored procedure that does this, with a working title of sp_ineachdb. The name reflects that it executes a command in the context of each database, instead of simply for each database. For now, I’ll leave out all the options I added to the original replacement:

USE [master];
GO

CREATE PROCEDURE dbo.sp_ineachdb
  @command nvarchar(max)
AS
BEGIN
  DECLARE @context nvarchar(150),
          @sx      nvarchar(18) = N'.sys.sp_executesql',
          @db      sysname;
  CREATE TABLE #dbs(name sysname PRIMARY KEY);

  INSERT #dbs(name) SELECT QUOTENAME(name) 
    FROM sys.databases
    WHERE [state] & 992 = 0 –- accessible
    AND DATABASEPROPERTYEX(name, 'UserAccess') <> 'SINGLE_USER' 
    AND HAS_DBACCESS(name) = 1;

  DECLARE dbs CURSOR LOCAL FAST_FORWARD
    FOR SELECT name, name + @sx FROM #dbs;

  OPEN dbs;

  FETCH NEXT FROM dbs INTO @db, @context;

  DECLARE @msg nvarchar(512) = N'Could not run against %s : %s.',
          @err nvarchar(max);

  WHILE @@FETCH_STATUS <> -1
  BEGIN

    BEGIN TRY
      EXEC @context @command = @command;
    END TRY
    BEGIN CATCH
      SET @err = ERROR_MESSAGE();
      RAISERROR(@msg, 1, 0, @db, @err);
    END CATCH

    FETCH NEXT FROM dbs INTO @db, @context;
  END

  CLOSE dbs; DEALLOCATE dbs;

END
GO

EXEC sys.sp_MS_marksystemobject N'sp_ineachdb';	

Stepping through the code, here’s what it does:

• Creates a table to hold the database names
• Inserts all the databases that are accessible, online, not single_user, and that the caller can access
• Creates a cursor to step through each name
• For each database:
  • Tries to execute the command in each database (I’ll explain how that works in a moment)
  • If it fails, raise a non-batch-aborting error (essentially a print stating what happened.

That’s it. Now, how the execution works, is that we can execute in the context of another database (or server even) by creating a string that represents [location].sys.sp_executesql. For example:

USE [master];
GO

DECLARE @context nvarchar(150) = N'tempdb.sys.sp_executesql';
EXEC @context @command = N'PRINT DB_NAME();';

What this is really doing is:

EXEC tempdb.sys.sp_executesql @command = N'PRINT DB_NAME();';

This executes the print statement in the context of tempdb, without ever having to switch to tempdb. In sp_ineachdb, we’re simply taking advantage of this by building @context dynamically. The QUOTENAME() protects you from bad identifiers, so go ahead and try this with a bunch of poorly-named databases:

CREATE DATABASE [12345];
CREATE DATABASE [1db'-.foo];
CREATE DATABASE [bad"name];
CREATE DATABASE [bad.name];
CREATE DATABASE [bad-name];
CREATE DATABASE [bad]][[.name];
CREATE DATABASE [bad[]]name];
CREATE DATABASE [bad name];
CREATE DATABASE [ leading space];
CREATE DATABASE [trailing space ];

These all work no problem:

EXEC dbo.sp_ineachdb @command = N'PRINT DB_NAME();';

Output:

 leading space
12345
1db'-.foo
bad name
bad"name
bad.name
bad[]name
bad][[.name
bad-name
master
model
msdb
tempdb
trailing space

This means that if you do want the calling context to mean something (say, you have a static, central table you want to join against everything else in all the other databases, you need to qualify the calling context in the command. As an example, you want to find all the objects with names that match a central list, which you store in a table in a database called Central.

CREATE TABLE dbo.ObjectNameBlacklist
(
  badname sysname
);

INSERT dbo.ObjectNameBlacklist(badname) VALUES(N'badword');

With sp_foreachdb, you would say:

EXEC dbo.sp_foreachdb N'SELECT ''?'', badname 
  FROM dbo.ObjectNameBlacklist AS onb
  WHERE EXISTS
  (
    SELECT 1 FROM ?.sys.objects WHERE name = onb.name COLLATE DATABASE_DEFAULT
  );';

(And admittedly you would get an error on at least one of the bad database names from above.)

With the new procedure, you would say (differences bolded):

EXEC dbo.sp_ineachdb N'SELECT DB_NAME(), badname 
  FROM Central.dbo.ObjectNameBlacklist AS onb
  WHERE EXISTS
  (
    SELECT 1 FROM sys.objects WHERE name = onb.name COLLATE DATABASE_DEFAULT
  );';	

This one does not fail on any of the bad database names, showing how much more resilient QUOTENAME() is compared to manually constructing your own quoted identifiers. More importantly, you can now pass commands without worrying about ?, how to escape it, or adding USE commands.

Another nice benefit is that nested INSERT … EXEC works. With sp_foreachdb, if you try this:

CREATE TABLE #c(db sysname, c int);
INSERT #c EXEC dbo.sp_foreachdb N'SELECT ''?'', COUNT(*) FROM ?.sys.objects;';
SELECT db,c FROM #c;
GO
DROP TABLE #c;

The result is an error message:

With at least this simple version of sp_ineachdb, it works, because there isn’t a nested INSERT … EXEC: 

CREATE TABLE #c(db sysname, c int);
INSERT #c EXEC dbo.sp_ineachdb N'SELECT DB_NAME(), COUNT(*) FROM sys.objects;';
SELECT db,c FROM #c;
GO
DROP TABLE #c;	

Summary

It is definitely possible to look at the “execute the same command against a bunch of databases” problem in a variety of ways. This stored procedure shows how to execute the command in each database’s context, rather than expect the command to provide tokens to slip the database name into. Depending on what you need to do and where the commands are coming from, this might be a better option, as it overcomes multiple limitations of both the native procedure and my replacement.

You can easily extend this solution by adding the other options I added for sp_foreachdb, to better dictate which databases to execute against. I didn’t do that here because I wanted to simplify the above to demonstrate how it works; I will address that in a follow-up tip (and will try to find a way to resolve the nested INSERT … EXEC issue).

Next Steps

Read on for related tips and other resources:

• Making a more reliable and flexible sp_MSforeachdb
• Run The Same SQL Command Against All SQL Server Databases
• Iterating through SQL Server databases and database objects without cursors
• Common uses for sp_MSforeachdb

About the author

Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips