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