The Simplest Alternative to sp_MSforeachdb

Which drawbacks are there to using the built-in sp_MSforeachdb stored procedure? What other alternatives are out there? Is there a simpler solution? Find out all this and more in this short post.

What is sp_MSforeachdb?

sp_MSforeachdb is a built-in stored procedure in SQL Server, the purpose of which is to execute a given T-SQL command for all databases, both system and user databases, regardless of their status or accessibility.

Even though this stored procedure is undocumented by Microsoft, there is ample information about it available across the internet, due to how useful it can be.

Therefore, I won’t be going into too much detail about it here.

Problems with sp_MSforeachdb

There are a few glaring problems with this stored procedure, though, which become apparent once your use case becomes just a tiny bit more complex than the obvious:

  1. The command parameter is limited to a maximum length of 2000 characters. This could be very problematic for longer-than-usual commands that need to be run for each database.
  2. The command is NOT executed within the context of each database iterated. You will have to include the “USE” clause inside your command, or explicitly reference the iterated database name. Which brings me to:
  3. The command relies on a “replacer character” (by default “?”), which serves as a placeholder for each database name in the iteration. However, since it’s essentially SQL injection, this could become a problem if your database name has problematic characters such as [, ], and single quote '. Such use cases could not be easily solved as long as you use sp_MSforeachdb.
  4. The command does not accept external parameters. Which means that you will have to rely on SQL injection if your command is even a little bit dynamic and dependent on external input.
  5. All databases are iterated regardless of status, accessibility, and whether they’re system or user databases. If you only need to iterate for certain databases based on status or type, that means you’d have to factor in some kind of check inside your command parameter, further inflating your code.

There are others

Obviously, I am not the first one to think of these problems.

Several people before me have already created alternative stored procedures to sp_MSforeachdb.

A few notable examples:

All of the above are good alternatives (with Aaron Bertrand’s sp_ineachdb being my favorite)

Not 100 Percent

While the alternatives mentioned above are very good, they don’t necessary resolve 100 percent of the issues with sp_MSforeachdb.

Specifically:

  • The command does not accept external parameters.
  • All alternatives require the creation of another stored procedure in your instance, which may not necessarily be possible or preferable in all cases.

Here’s a script

So what can we do to resolve the shortcomings of the alternative procedures?

Well, personally, I would resort to writing my own script involving a CURSOR.

Besides having to copy-and-paste the block of code below and then adjust it as needed, it should resolve all the shortcomings mentioned above.

So, without further ado, here is my own version of the simplest possible alternative to sp_MSforeachdb:

/*
Author: Eitan Blumin | https://eitanblumin.com | https://madeiradata.com
Description:
This is the simplest possible alternative to sp_MSforeachdb which is not too great.
Instructions:
1. Replace the contents of the @Command variable with the command you want to run INSIDE each database.
2. Replace the contents of the @Parameters variable with the parameters you want the command to receive.
3. Add parameters as needed, given @p1 as an example.
4. Change the database filter predicates in the cursor declaration, as needed.
Remarks:
– The command will be run within the context of each online database in the SQL Server instance.
– This version does NOT support the "?" replacer character.
– Instead, you can use DB_NAME() to get the name of the current database context.
*/
SET NOCOUNT, XACT_ABORT, ARITHABORT ON;
DECLARE @Command nvarchar(max) = N'PRINT DB_NAME() + N'': '' + @p1'
DECLARE @Parameters nvarchar(max) = N'@p1 nvarchar(100)'
DECLARE @p1 nvarchar(100) = N'I am @p1'
DECLARE @CurrDB sysname, @spExecuteSQL NVARCHAR(1000)
DECLARE DBs CURSOR
LOCAL FAST_FORWARD
FOR
SELECT [name]
FROM sys.databases WITH (NOLOCK)
WHERE state = 0 /* online only */
AND HAS_DBACCESS([name]) = 1 /* accessible only */
AND database_id > 4 AND is_distributor = 0 /* ignore system databases */
AND DATABASEPROPERTYEX([name], 'Updateability') = 'READ_WRITE' /* writeable only */
OPEN DBs
WHILE 1=1
BEGIN
FETCH NEXT FROM DBs INTO @CurrDB;
IF @@FETCH_STATUS <> 0 BREAK;
SET @spExecuteSQL = QUOTENAME(@CurrDB) + N'..sp_executesql'
EXEC @spExecuteSQL @Command, @Parameters, @p1 /* add or remove parameters here as needed */
WITH RECOMPILE; use RECOMPILE to avoid storing in plan cache for each DB
END
CLOSE DBs;
DEALLOCATE DBs;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.