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:
- 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.
- 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:
- 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. - 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.
- 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:
- sp_foreachdb | Aaron Bertrand
- sp_ineachdb | Aaron Bertrand (also featured in Brent Ozar’s First Responder Kit)
- dba_ForEachDB | Spaghetti DBA
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: