A reliable and flexible replacement for sp_MSforeachdb
Originally published December 2010; updated January 2020
The system procedure,
sp_MSforeachb, is often used when you want to run the same command against all databases. But it is no bueno. It is undocumented, unsupported, and has a known but unresolved bug where it can skip databases due to the type of cursor it uses. Microsoft won't acknowledge the bug because doing so would be
admitting Fight Club exists.
So, I wrote a replacement,
sp_foreachdb, which has a lot more flexibility, and does not exhibit the "skip databases" bug. This procedure became a part of Brent Ozar's First Responder Kit in 2016, but was recently deprecated. It has a few issues of its own; for example, it can't be used as the target of
INSERT ... EXEC, and it can't run commands like
DB_NAME() in the context of the selected database.
The new replacement,
sp_ineachdb, which I wrote about here and here, is preferred over the above options. It truly runs each command in its database, is easily extensible, and has fewer obstacles for implementation into your own code (for example, it *can* be the target of
INSERT ... EXEC).
You're still on your own in terms of support, though contributors have stepped up to make improvements. And just recently, after a ping from a colleague, I added a new argument,
@exclude_pattern. Zach wanted to be able to exclude all databases that matched a pattern (e.g. skip all databases with names that start with
dba_%). This simple pull request – targeted for the February release – allows you to do just that, though if you wanted to exclude more than one pattern in the same call, you'd have to modify the procedure further (think
@exclude_pattern2, and so on).