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

By:   |   Updated: 2018-10-04   |   Comments (6)   |   Related: More > T-SQL

Smashing SQL Server Silos with DreamFactory

Free MSSQLTips Webinar: Smashing SQL Server Silos with DreamFactory

DreamFactory is an API management platform best known for its ability to generate REST APIs for a wide variety of data sources, including Microsoft SQL Server. Organizations around the globe use the platform to reduce if not entirely eliminate the time and overhead otherwise required to manually build MS SQL Server-backed APIs.


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.


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;
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;
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];

CREATE PROCEDURE dbo.sp_ineachdb
  @command nvarchar(max)
  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 HAS_DBACCESS(name) = 1;

    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);


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

    FETCH NEXT FROM dbs INTO @db, @context;



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];

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 [1db'-.foo];
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();';


 leading space
bad name
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
    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
    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;

The result is an error message:

Msg 8164, Level 16, State 1, Procedure dbo.sp_foreachdb, Line 87 [Batch Start Line 0]
An INSERT EXEC statement cannot be nested.

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;


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:

Last Updated: 2018-10-04

get scripts

next tip button

About the author
MSSQLTips author Aaron Bertrand 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.

View all my tips

Comments For This Article

Thursday, February 28, 2019 - 7:14:25 AM - Aaron Bertrand Back To Top (79154)

Richard, the bug with sp_MSForEachDB is not that it misses "oddly named databases" - it misses arbitrary databases, intermittently, having nothing at all to do with their names. It is undocumented and unsupported for a reason. :-)

Monday, February 25, 2019 - 2:33:49 PM - Richard Foster Back To Top (79124)


I don't think I was very clear, I only type the "Set" statement once then I used sp_MSForEachDB.  It accepts multiple commands when you separate by semi-colon, I insert the "use dbname" in front of the command set to execute for each database then all commands run under the context of the database in the set statement. I also use SSMS to execute the whole sets over many servers using sp_MSForEachDB in each server.  However, I do see the use for your procedure for oddly named databases and learned that sp_MSForEachDB misses those. Thanks for sharing, Rick

Monday, February 25, 2019 - 1:35:53 PM - Aaron Bertrand Back To Top (79121)

Richard, sure, typing out a USE command is easy. When you need to run the code 2 or 3 times. 200? Not so much. :-)

Monday, February 25, 2019 - 10:57:27 AM - Richard Foster Back To Top (79118)

 Nice, however FWIW, I find it just as easy to execute a "Use DBName;" then the command I want to execute, this does return the database name when using Select DBName(), as I use that embedded in the query to get which database is return the result. 

Saturday, December 01, 2018 - 10:21:04 AM - Aaron Bertrand Back To Top (78383)

Hey Robert, neither of those were my ideas. They came from these lines in sp_MSforeachdb, and pre-date sys.databases:

   N' where (d.status & ' + @inaccessible + N' = 0)' +  -- my comment: this @inaccessible resolves to 992
   N' and (DATABASEPROPERTYEX(d.name, ''UserAccess'') <> ''SINGLE_USER'' and (has_dbaccess(d.name) = 1))' )  

I could have simplified one or both, sure, but I wanted to stay as true to the parts of the original that work as I could (while tossing the parts that don't work).

Saturday, December 01, 2018 - 6:22:36 AM - Robert Steen Back To Top (78381)

Could you please clarify 2 things to do with the querying of sys.databases:

1. Why mask "state"? According to BOL, it is a tinyint with a sequential value, not a bitmask.

2. Why use "DATABASEPROPERTYEX(name, 'UserAccess') <> 'SINGLE_USER'", when "user_access" is available in sys.databases?

But, in any case, very nice. Thank you.


Recommended Reading

SQL Server Cursor Example

Using MERGE in SQL Server to insert, update and delete at the same time

Rolling up multiple rows into a single row and column for SQL Server data

SQL Server Loop through Table Rows without Cursor


get free sql tips
agree to terms