Making a more reliable and flexible sp_MSforeachdb
By: Aaron Bertrand | Comments (52) | Related: More > Scripts
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. 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. I first blogged about it back in 2010, then again when this tip was published. In 2016, it was added to Brent Ozar Unlimited's SQL Server First Responder Kit, where community members help contribute fixes and enhancements.
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.
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.
@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.
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:
@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).
@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:
@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:
About the author
Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.
View all my tips