A more reliable and more flexible sp_MSforeachdb

AaronBertrand — Wed, 29 Dec 2010 16:02:00 GMT

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:

http://sqlblog.com/blogs/aaron_bertrand/archive/2010/02/08/bad-habits-to-kick-relying-on-undocumented-behavior.aspx

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';

...or...

EXEC sp_MSforeachdb N'IF EXISTS 
(
    SELECT 1 FROM sys.databases WHERE name = ''?'' 
    AND recovery_model_desc = N''SIMPLE''
)
BEGIN
    -- do something
END';

...or...

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 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." 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:

while it supports database names containing some common "naughty" characters - namely, periods (.), double-quotes ("), and left and right square brackets ([ ])), as well as database names that start with a number, it does not support databases named with single-quotes (') or leading / trailing spaces. The reason is that the database names are parsed in various ways to support the different filtering types, and it became very tedious to programmatically determine where I had to double-up single quotes and where I couldn't, or where I had to QUOTENAME() the name and where I couldn't.
it made me feel weak using dynamic SQL, but it was the easiest way I could think of (without creating supplemental functions) to add the IN () list when a comma-separated list of databases was passed in. Thinking back I could have likely used a table variable to hold the names after splitting, but on the plus side, the dynamic SQL makes adding optional filter clauses quite routine.
I only implemented a few filtering options that I thought would be most commonly required. The model is fairly simple to extrapolate if you have other criteria in mind.
another feature request was sorting. Given that dynamic SQL is already in use, it would be easy enough to add a clause like '... ORDER BY ' + @order_by + ' ' + @order_by_direction. However, from the tone of the conversation, it didn't seem to me like it was simple column ordering that was desired; rather, people were after very custom things like "process the full recovery databases first, then bulk-logged, then simple" or "process the system databases first, then the user databases that contain the string 'foo', then the rest of the user databases in alphabetical order." Obviously this kind of sorting without defined limits could quickly become a rat's nest.

Anyway, once again, I hope you find my creation usable.

Search results matching tags 'Maintenance' and 'sp_MSforeachdb'

A more reliable and more flexible sp_MSforeachdb