sp_MSforeachdb 대체 할 만한 SP

Sql Server에서 제공하는 SP중 비공식문서에만 존재하는 sp_MSforeachdb 에 몇가지 기능을 추가한 SP를 웹에서 발견하였다.

Problem

While the system procedure sp_MSforeachdb is neither documented nor officially supported, most SQL Server professionals have used it at one time or another. This is typically for ad hoc maintenance tasks, but many people (myself included) have used this type of looping activity in permanent routines. Sadly, I have discovered instances where, under heavy load and/or with a large number of databases, the procedure can actually skip multiple catalogs with no error or warning message. Since this situation is not easily reproducible, and since Microsoft typically has no interest in fixing unsupported objects, this may be happening in your environment right now.

Solution

In my environment, the minute I discovered the issue, I promptly wrote a replacement. (I blogged about my replacement earlier this year.) While I was writing the new stored procedure, it struck me that, while I was making my maintenance processes more reliable, I could also make them more flexible.

For example, I could have the procedure operate only on databases that:

• are system databases (master, msdb, model, tempdb);
• are non-system databases;
• match a specific name pattern;
• are in a comma-separated list of db names;
• have a specific recovery model or compatibility level;
• are read only or have auto-close or auto-shrink enabled; or,
• have service broker enabled.

There are, of course, dozens of other properties that you could look at - but those were the main elements I could envision a need to filter on. Some of them turned out to be more complex to implement than I had initially envisioned. For example, taking a comma-separated list of database names (e.g. 'master, model') and turning them into a comma-separated list of string-delimited database names (e.g. N'master', N'model') for use in an IN () query made me turn to dynamic SQL.

Some other handy options I thought to add, which aren't in sp_MSforeachdb, include an option to print the database name before each result, or even to only print the command instead of executing. This can be very handy if you are trying to set a slew of databases to SINGLE_USER and don't want the operations to happen serially; you can print the commands and split the output across multiple Management Studio windows.

With all that said, here is the stored procedure in its current form:
■ 소스보기 ↓

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,'</x><x>',','),

           '</x>',''),'<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

Caveats

The procedure doesn't cope well with databases with a single quote ( ' ) in their name or with leading / trailing spaces, but it gladly handles databases that violate other best practices, such as beginning with a number or containing special characters like ., ", [, or ]. Here is a quick list of databases that it has been tested against:

(Try creating those databases on your system and running EXEC sp_MSforeachdb 'SELECT * FROM ?.sys.objects;'; - you'll get a variety of errors.)

Also, you do not need to QUOTENAME parameter values... you should pass in 'master, model' to @database_list, not '[master], [model]', and you should use 'USE ?;' and not 'USE [?];' for the command and replace_character values - this escaping is handled for you. However, if you have a command where you want to be able to selectively choose whether or not to apply QUOTENAME to the replace_character (for example, @command = 'SELECT ''[?]'', * FROM sys.databases WHERE name = ''?'';'), you can use the override parameter @suppress_quotename.

While there are parsing solutions for all of these problems, they quickly explode the code and become more maintenance trouble than they're worth. At least, in this author's opinion.

Finally, the procedure does not currently include any logging or error handling, which you may want to add if you are going to use this type of procedure in any automated processes.

---

Examples

To perform a full backup to the same folder of all user databases that are in simple mode. This is one case where you'll want to use the @suppress_quotename parameter, otherwise you end up with files named [database_name].bak.

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;

To search all databases matching the name pattern 'Company%' for objects matching the name pattern '%foo%'. Place into a #temp table so the result is a single result set instead of the number of databases that match the naming pattern.

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;

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

To find the last created object date/time for each database in a defined set (in this case, three databases that I know exist).

EXEC sp_foreachdb
@command = N'SELECT N''?'', MAX(create_date) FROM ?.sys.objects;',
@database_list = 'master,model,msdb';

To reset service broker for every database - after testing an application, for example:

EXEC sp_foreachdb
@command = N'ALTER DATABASE ? SET NEW_BROKER;',
@is_broker_enabled = 1;

Next Steps

• Create your own version of sp_foreachdb in master.
• Add logging and/or error handling according to the standards and conventions in your environment.
• Begin phasing the new procedure into processes where you are currently using sp_MSforeachdb.
• Review the following tips and other resources:
  • Run The Same SQL Command Against All SQL Server Databases
  • Iterating through SQL Server databases and database objects without cursors
  • Common uses for sp_MSforeachdb
  • Splitting Delimited Strings Using XML in SQL Server