SQL Server professionals often rely on the undocumented and unsupported system procedure sp_MSforeachdb for ad hoc maintenance tasks. However, this procedure can sometimes skip multiple databases without any error or warning messages, especially under heavy load or with a large number of databases.

To address this issue, a replacement stored procedure called sp_foreachdb was developed. This new procedure not only improves the reliability of maintenance processes but also adds flexibility. It allows users to filter databases based on various criteria such as system databases, non-system databases, specific name patterns, comma-separated lists of database names, recovery models, compatibility levels, read-only status, auto-close or auto-shrink settings, and service broker enabled status.

Here is an example of the stored procedure:

USE [master];
GO

CREATE PROCEDURE dbo.sp_foreachdb
    @command NVARCHAR(MAX),
    @replace_character NCHAR(1) = N'?',
    @print_dbname BIT = 0,
    @print_command_only BIT = 0,
    @suppress_quotename BIT = 0,
    @system_only BIT = NULL,
    @user_only BIT = NULL,
    @name_pattern NVARCHAR(300) = N'%',
    @database_list NVARCHAR(MAX) = NULL,
    @recovery_model_desc NVARCHAR(120) = NULL,
    @compatibility_level TINYINT = NULL,
    @state_desc NVARCHAR(120) = N'ONLINE',
    @is_read_only BIT = 0,
    @is_auto_close_on BIT = NULL,
    @is_auto_shrink_on BIT = NULL,
    @is_broker_enabled BIT = NULL
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(MAX),
            @dblist NVARCHAR(MAX),
            @db NVARCHAR(300),
            @i INT;

    IF @database_list > N''
    BEGIN
        WITH n (n) AS
        (
            SELECT ROW_NUMBER() OVER (ORDER BY s1.name) - 1
            FROM sys.objects AS s1
            CROSS JOIN sys.objects AS s2
        )
        SELECT @dblist = REPLACE(REPLACE(REPLACE(x, '', ','), '', ''), '', '')
        FROM
        (
            SELECT DISTINCT x = 'N''' + LTRIM(RTRIM(SUBSTRING(@database_list, n, CHARINDEX(',', @database_list + ',', n) - n))) + ''''
            FROM n
            WHERE n <= LEN(@database_list)
            AND SUBSTRING(',' + @database_list, n, 1) = ','
            FOR XML PATH('')
        ) AS y(x);
    END

    CREATE TABLE #x (db NVARCHAR(300));

    SET @sql = N'SELECT name FROM sys.databases WHERE 1=1'
                + CASE WHEN @system_only = 1 THEN ' AND database_id IN (1,2,3,4)' ELSE '' END
                + CASE WHEN @user_only = 1 THEN ' AND database_id NOT IN (1,2,3,4)' ELSE '' END
                + CASE WHEN @name_pattern <> N'%' THEN ' AND name LIKE N''%' + REPLACE(@name_pattern, '''', '''''') + '%''' ELSE '' END
                + CASE WHEN @dblist IS NOT NULL THEN ' AND name IN (' + @dblist + ')' ELSE '' END
                + CASE WHEN @recovery_model_desc IS NOT NULL THEN ' AND recovery_model_desc = N''' + @recovery_model_desc + '''' ELSE '' END
                + CASE WHEN @compatibility_level IS NOT NULL THEN ' AND compatibility_level = ' + RTRIM(@compatibility_level) ELSE '' END
                + CASE WHEN @state_desc IS NOT NULL THEN ' AND state_desc = N''' + @state_desc + '''' ELSE '' END
                + CASE WHEN @is_read_only IS NOT NULL THEN ' AND is_read_only = ' + RTRIM(@is_read_only) ELSE '' END
                + CASE WHEN @is_auto_close_on IS NOT NULL THEN ' AND is_auto_close_on = ' + RTRIM(@is_auto_close_on) ELSE '' END
                + CASE WHEN @is_auto_shrink_on IS NOT NULL THEN ' AND is_auto_shrink_on = ' + RTRIM(@is_auto_shrink_on) ELSE '' END
                + CASE WHEN @is_broker_enabled IS NOT NULL THEN ' AND is_broker_enabled = ' + RTRIM(@is_broker_enabled) ELSE '' END;

    INSERT #x EXEC sp_executesql @sql;

    DECLARE c CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
        SELECT CASE WHEN @suppress_quotename = 1 THEN db ELSE QUOTENAME(db) END
        FROM #x
        ORDER BY db;

    OPEN c;
    FETCH NEXT FROM c INTO @db;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sql = REPLACE(@command, @replace_character, @db);

        IF @print_command_only = 1
        BEGIN
            PRINT '/* For ' + @db + ': */' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + @sql + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10);
        END
        ELSE
        BEGIN
            IF @print_dbname = 1
            BEGIN
                PRINT '/* ' + @db + ' */';
            END

            EXEC sp_executesql @sql;
        END

        FETCH NEXT FROM c INTO @db;
    END

    CLOSE c;
    DEALLOCATE c;
END
GO

The sp_foreachdb stored procedure provides several advantages over sp_MSforeachdb. It allows for more precise filtering of databases based on specific criteria, such as recovery model, compatibility level, read-only status, and more. It also includes additional options like printing the database name before each result or only printing the command instead of executing it.

Here are some examples of how sp_foreachdb can be used:

-- Perform a full backup to the same folder of all user databases that are in simple mode
EXEC sp_foreachdb
    @command = N'BACKUP DATABASE [?] TO DISK = ''C:\backups\?.bak'' WITH INIT, COMPRESSION;',
    @user_only = 1,
    @recovery_model_desc = N'SIMPLE',
    @suppress_quotename = 1;

-- Search all databases matching the name pattern 'Company%' for objects matching the name pattern '%foo%'
-- Place the result into a #temp table for a single result set
CREATE TABLE #x(n SYSNAME);
EXEC sp_foreachdb
    @command = N'INSERT #x SELECT name FROM ?.sys.objects WHERE name LIKE N''%foo%'';',
    @name_pattern = N'Company%';
SELECT * FROM #x;
DROP TABLE #x;

-- Turn auto_shrink off for all databases where it is enabled
EXEC sp_foreachdb
    @command = N'ALTER DATABASE ? SET AUTO_SHRINK OFF;',
    @is_auto_shrink_on = 1;

-- Find the last created object date/time for each database in a defined set
EXEC sp_foreachdb
    @command = N'SELECT N''?'', MAX(create_date) FROM ?.sys.objects;',
    @database_list = 'master,model,msdb';

-- Reset service broker for every database
EXEC sp_foreachdb
    @command = N'ALTER DATABASE ? SET NEW_BROKER;',
    @is_broker_enabled = 1;

It’s important to note that the sp_foreachdb stored procedure has some limitations. It may not handle databases with a single quote (‘) in their name or leading/trailing spaces properly. Additionally, it does not include logging or error handling, which may need to be added for automated processes.

Overall, sp_foreachdb provides a more reliable and flexible solution for performing maintenance tasks on multiple databases in SQL Server.