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.
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;
IF @database_list > N'' BEGIN ;WITH n(n) AS ( SELECT ROW_NUMBER() OVER (ORDER BY s1.name) - 1 FROM sys.objects AS s1 CROSS JOINsys.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 BYdb;
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 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.
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:
Very useful; thanks for creating it & posting it. If you're considering any enhancements to it, one feature I occasionally wish for, is the capability to select "all-except" certain databases. Perhaps implemented like Ola Hallengren does, by preceeding DB names with a dash if you want them to be skipped. Anyway, thanks again.
Wednesday, December 29, 2010 - 10:13:49 AM - johns
I had a bad feeling about these stored procedures (sp_MSforeachtable, sp_MSforeachDB, sp_MSforeach_Worker) a good while back also when I saw them using a global cursor. I also took a stab at re-writing them into a single stored proc. I mostly followed along the logic of the original fixing what was bad practice and encapsulated everything in a try/catch. However I replace what scared me the most about them, the global cursor, with a dynamic local cursor. I set it up the following way:
/* Create the SELECT */ DECLARE @SQL nvarchar(max); IF @worker_type = 1 BEGIN SET deadlock_priority low;
SET @SQL = N'SET @my_cur = CURSOR LOCAL FAST_FORWARD FOR ' + N'SELECT name ' + N' FROM master.dbo.sysdatabases d ' + N' WHERE (d.status & ' + @inaccessible + N' = 0)' + N' AND (DATABASEPROPERTY(d.name, ''issingleuser'') = 0 AND (has_dbaccess(d.name) = 1))'; END ELSE IF @worker_type = 0 BEGIN SET @SQL = N'SET @my_cur = CURSOR LOCAL FAST_FORWARD FOR ' + N'SELECT ''['' + REPLACE(schema_name(syso.schema_id), N'']'', N'']]'') + '']'' + ''.'' + ''['' + REPLACE(object_name(o.id), N'']'', N'']]'') + '']'' ' + N' FROM dbo.sysobjects o ' + N' INNER JOIN sys.all_objects syso on o.id = syso.object_id ' + N' WHERE OBJECTPROPERTY(o.id, N''IsUserTable'') = 1 ' + N' AND o.category & ' + @mscat + N' = 0 '; END ELSE BEGIN RAISERROR 55555 N'Util_ForEach_TableOrDB assert failed: wrong Type selected'; END;
IF @whereand IS NOT NULL BEGIN SET @SQL = @SQL + @whereand; END; SET @SQL = @SQL + N'; OPEN @my_cur;';
/* DO the work here */ create table #qtemp ( /* Temp command storage */ qnum int NOT NULL, qchar nvarchar(2000) COLLATE database_default NULL );
/* Get all tables or DBs to do something to */ DECLARE @local_cursor cursor EXEC sp_executesql @SQL ,N'@my_cur cursor OUTPUT' ,@my_cur = @local_cursor OUTPUT;
FETCH @local_cursor INTO @name;
/****** BUNCH OF CODE here to do the processing as before ******/
SET @curStatus = Cursor_Status('variable', '@local_cursor'); IF @curStatus >= 0 BEGIN CLOSE @local_cursor; DEALLOCATE @local_cursor; END;
Thursday, February 10, 2011 - 3:50:20 PM - Aaron Bertrand
To address some of the Dynamic T-SQL you can also create a user defined table-valued function that returns a table. We use this a lot! In our case, we use semi-colon as the delimiter, however you could change the function to use a different delimeter if you need to.
@ParsedString table (RetStr varchar(80
-- Assumes the returned parced value is 80 characters
DECLARE @column varchar(80), @Pos int
SET @List =LTRIM(RTRIM(@List))+';'
SET @Pos =CHARINDEX(';',@List,1)
-- If there are values in the list, go and process parameters
WHILE @Pos > 0
SET @column =LTRIM(RTRIM(LEFT(@List, @Pos - 1)))
IF @column <>''
INSERTINTO @ParsedString(RetStr)VALUES (@column)
SET @List =RIGHT(@List,LEN(@List)- @Pos)
SET @Pos =CHARINDEX(';', @List, 1)
DECLARE @DBExclusionList varchar(2000)
SET @DBExclusionList = 'column1;column2;column3'
SELECT RetStr FROM dbo.udfMT_ParsedString(@DBExclusionList);
Results look as such:
column1 column2 column3
With it returning a table, you can even use it in inner joins or in statement as such:
Friday, July 06, 2012 - 4:09:30 PM - Larry Silverman
@div, SQL Server doesn't really have an export to CSV functionality. Regardless of the query you're using (sp_MSforeachdb, my replacement, or something else entirely), you'll need to combine it with something else to export to CSV. For example you can use PowerShell to run your query and then save it to a file using export-csv.
I discovered an issue when using a non-trivial number of databases in the @database_list parameter in conjunction with running the stored procedure under an account with somewhat limited permissions. The @database_list parameter value needs to get parsed and adjusted, which relies on using the sys.objects table as a bit of "numbers list". This numbers list needs to fully cover the length of the @database_list parameter, or else some of the latter DB names might get truncated.
I blogged about a tweak I made to help ensure that you can always properly parse out the @database_list even if the current user doesn't have enough permissions to select an adequate number of rows out of sys.objects:
Hi I got your script and ran it against the script below and got an error for nesting EXEC's "An INSERT EXEC statement cannot be nested. at Line 86" hope this helps.
SET NOCOUNT ON DECLARE @AllTables table (CompleteTableName nvarchar(4000)) INSERT INTO @AllTables (CompleteTableName) EXEC usp_foreachdb 'select @@SERVERNAME+''.''+''?''+''.''+s.name+''.''+t.name from [?].sys.tables t inner join sys.schemas s on t.schema_id=s.schema_id' SET NOCOUNT OFF SELECT * FROM @AllTables ORDER BY 1
Thursday, September 05, 2013 - 7:07:20 PM - Aaron Bertrand
EXEC usp_foreachdb @user_only=1, @command=N' USE ?; INSERT #AllTables SELECT ''?'' ,TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=''BASE TABLE'' AND TABLE_NAME=''InternalVersion'''; SELECT * FROM #AllTables
and tested on SQL Server 12.0.2000 SQL Server correct 4 part naming convention is [linkserver].[database].[schema].[object] I will encourage everybody to follow 4 part naming convention, specially the [schema] many people have forgotten to do. (or don't understand)