A reliable and flexible replacement for sp_MSforeachdb
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.
In 2010, 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. I'm not the only one, but here are two pull requests I've submitted:
- In January, 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).
- Today I added logic (#2508) to fix an issue raised by Andy Mallon (#2507), where he suggested that distribution databases should be considered system.