Updated July 2018
I've complained about sp_MSforeachdb before. In part of my "Bad Habits to Kick" series in 2009-10, I described how I worked around its sporadic inability to actually process all of the databases on an instance:
I lumped this in a "Bad Habit" category of relying on undocumented behavior, since – while the procedure does have rampant usage – it is, in fact, both undocumented and unsupported. That said, it can be quite useful to have a procedure like this, but only if you can rely on it.
Last week, the topic came up on twitter, where someone asked if they had ever seen sp_MSforeachdb miss databases. I raised my hand. The conversation continued beyond my pointing how I worked around the problem; in addition to skipping databases, the built-in option also had difficulty with poorly-named databases such as "foo.com" and "I am a [bracket]." We soon came to the conclusion that there was plenty of room for other improvements, too. For example, why not have a parameter to support only system databases, or only user databases, or only databases in a certain list or matching a certain string pattern? Why not extend that to other properties, such as recovery model, state, and whether auto-close was enabled? And why not support printing the commands instead of executing them, so that you can selectively execute them in multiple windows, or change the order manually? Having performed many maintenance tasks where I did things like:
EXEC sp_MSforeachdb N'IF ''?'' LIKE ''%pattern%'' BEGIN -- do something END';
EXEC sp_MSforeachdb N'IF EXISTS ( SELECT 1 FROM sys.databases WHERE name = ''?'' AND recovery_model_desc = N''SIMPLE'' ) BEGIN -- do something END';
EXEC sp_MSforeachdb N'IF DATABASEPROPERTYEX(''?'', ''IsAutoClose'') = 1 BEGIN PRINT ''ALTER DATABASE [?] SET AUTO_CLOSE OFF;''; END';
…I thought that I should encapsulate all of those different features into a single procedure,
sp_foreachdb, that I could deploy onto all of the instances I manage. And I thought that it would be useful to share this procedure with others. So, this past week, I wrote a tip for mssqltips.com, entitled, much like this post, "Making a more reliable and flexible sp_MSforeachdb."
In 2016, this procedure was added to the Brent Ozar Unlimited SQL Server First Responder Kit, where users in the community contribute enhancements.
With more optimized cursor code and plenty of filtering capabilities, I hope that you'll find my implementation a suitable replacement. I did leave a few things as an exercise for the reader:
Anyway, once again, I hope you find my creation usable.