THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Aaron Bertrand

Aaron is a Product Evangelist for SentryOne, makers of Plan Explorer and a performance monitoring and event management platform for the Microsoft Data Platform and VMware. He has been blogging here at sqlblog.com since 2006, focusing on manageability, performance, and new features, and also blogs at blogs.sentryone.com and SQLPerformance.com; has been a Microsoft MVP since 1997; tweets as @AaronBertrand; and speaks frequently at major conferences, user group meetings, and SQL Saturday events worldwide.

A more reliable and more flexible sp_MSforeachdb

I've complained about sp_MSforeachdb before.  In part of my "Bad Habits to Kick" series in 2009-10, I described how I worked around its sporadic inability to actually process all of the databases on an instance:

http://sqlblog.com/blogs/aaron_bertrand/archive/2010/02/08/bad-habits-to-kick-relying-on-undocumented-behavior.aspx

I lumped this in a "Bad Habit" category of relying on undocumented behavior, since - while the procedure does have rampant usage - it is, in fact, both undocumented and unsupported.  That said, it can be quite useful to have a procedure like this, but only if you can rely on it.

Last week, the topic came up on twitter, where someone asked if they had ever seen sp_MSforeachdb miss databases.  I raised my hand.  The conversation continued beyond my pointing how I worked around the problem; in addition to skipping databases, the built-in option also had difficulty with poorly-named databases such as "foo.com" and "I am a [bracket]."  We soon came to the conclusion that there was plenty of room for other improvements, too.  For example, why not have a parameter to support only system databases, or only user databases, or only databases in a certain list or matching a certain string pattern?  Why not extend that to other properties, such as recovery model, state, and whether auto-close was enabled?  And why not support printing the commands instead of executing them, so that you can selectively execute them in multiple windows, or change the order manually?  Having performed many maintenance tasks where I did things like:

EXEC sp_MSforeachdb N'IF ''?'' LIKE ''%pattern%''
BEGIN
    -- do something
END'
;

...or...

EXEC sp_MSforeachdb N'IF EXISTS 
(
SELECT 1 FROM sys.databases WHERE name = ''?''
AND recovery_model_desc = N''SIMPLE''
)
BEGIN
    -- do something
END'
;

...or...

EXEC sp_MSforeachdb N'IF DATABASEPROPERTYEX(''?'', ''IsAutoClose'') = 1
BEGIN
    PRINT ''ALTER DATABASE [?] SET AUTO_CLOSE OFF;'';
END'
;

...I thought that I should encapsulate all of those different features into a single procedure that I could deploy onto all of the instances I manage.  And I thought that it would be useful to share this procedure with others.  So, this past week, I wrote a tip for mssqltips.com, entitled, much like this post, "Making a more reliable and flexible sp_MSforeachdb."  With more optimized cursor code and plenty of filtering capabilities, I hope that you'll find my implementation a suitable replacement.  I did leave a few things as an exercise for the reader:

  • while it supports database names containing some common "naughty" characters - namely, periods (.), double-quotes ("), and left and right square brackets ([ ])), as well as database names that start with a number, it does not support databases named with single-quotes (') or leading / trailing spaces.  The reason is that the database names are parsed in various ways to support the different filtering types, and it became very tedious to programmatically determine where I had to double-up single quotes and where I couldn't, or where I had to QUOTENAME() the name and where I couldn't.

  • it made me feel weak using dynamic SQL, but it was the easiest way I could think of (without creating supplemental functions) to add the IN () list when a comma-separated list of databases was passed in.  Thinking back I could have likely used a table variable to hold the names after splitting, but on the plus side, the dynamic SQL makes adding optional filter clauses quite routine.

  • I only implemented a few filtering options that I thought would be most commonly required.  The model is fairly simple to extrapolate if you have other criteria in mind.

  • another feature request was sorting.  Given that dynamic SQL is already in use, it would be easy enough to add a clause like '... ORDER BY ' + @order_by + ' ' + @order_by_direction.  However, from the tone of the conversation, it didn't seem to me like it was simple column ordering that was desired; rather, people were after very custom things like "process the full recovery databases first, then bulk-logged, then simple" or "process the system databases first, then the user databases that contain the string 'foo', then the rest of the user databases in alphabetical order."  Obviously this kind of sorting without defined limits could quickly become a rat's nest.
Anyway, once again, I hope you find my creation usable.
 
Published Wednesday, December 29, 2010 12:02 PM by AaronBertrand

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Adam Machanic said:

I've also seen sp_MSforeachdb miss databases -- did you ever figure out why that happens?

December 29, 2010 11:24 AM
 

AaronBertrand said:

Nope, just know that I can't rely on it (and maybe it explains why it's undocumented / unsupported - given how simple the basic approach needs to be, it shouldn't be this way).

December 29, 2010 3:38 PM
 

Rob Farley said:

I've rolled my own before. I use UNION ALL to combine the results into a single resultset.

Nicely done though - I didn't do all the stored procedures for the different options.

December 29, 2010 9:38 PM
 

Raul Santos Neto said:

Very nice approach.. it's very useful. But do you remember from your conversation with the guys on Twitter, the cases you guys were unable to list some of the databases? Because by looking internally at the code from sp_MSforeachdb and sp_MSforeach_worker, you can find "lots" of IF's and WHERE clauses that could limit the rows (databases) returned by the original sp. Your version is more "transparent". MS could definitely improve their version. Cheers!

December 30, 2010 1:48 PM
 

Jeff Stanlick said:

I remember reading this at the New Year and thinking, "That's kinda cool but I haven't had any problems with sp_msforeachdb and I have bigger fish to fry." Then this morning happened. Thanks for posting a solution to my problem 3 months before I needed it!

March 28, 2011 9:19 AM
 

Aaron Bertrand said:

Christina Leo ( @christinaleo ) asked this question on twitter today: So, if I ever needed a POSH script,

October 20, 2011 2:08 PM
 

Liam North said:

I've tried using your stored procedure to search for stored procedures that may be open to SQL Injection by using the statement shown below, but it seems to be a bit hit and miss in that it will report stored procs that do contain EXEC (, but also many that don't. What am I doing wrong?

EXEC dbo.sp_foreachdb

      @command = N'INSERT #results SELECT name, N''?''

              FROM ?.sys.procedures

              WHERE OBJECT_DEFINITION(object_id) LIKE ''%EXEC (%'';'

October 28, 2011 3:19 AM
 

AaronBertrand said:

Hi Liam, I have two potential explanations.

1) Is it possible some of your "EXEC (" syntax actually has "EXEC(" or "EXEC (" where the space is actually a tab or more than one space?

2) Also, can you please ensure that you use ?.sys.sql_modules, since OBJECT_DEFINITION(object_id) will run in the calling database, not the target - meaning you will only reliably capture procedures in the database where you run the command. Your query should be:

EXEC dbo.sp_foreachdb

     @command = N'INSERT #results SELECT p.name, N''?''

             FROM [?].sys.procedures AS p

             INNER JOIN [?].sys.sql_modules AS m

             ON p.[object_id] = m.[object_id]

             WHERE m.definition LIKE ''%EXEC (%'';';

October 28, 2011 8:47 AM
 

Liam North said:

Thanks Aaron. I found that I came up against the 128 character limit for the sys.procedures.name column when trying this so what I ended up doing was this:

CREATE TABLE #procs([db_name] varchar(50), proc_name varchar(255), [object_id] int )

EXEC dbo.sp_foreachdb

@command = N'INSERT #procs SELECT N''?'', name, [object_id] FROM ?.sys.procedures'

CREATE TABLE #modules([db_name] varchar(50), definition nvarchar(max), [object_id] int )

EXEC dbo.sp_foreachdb

@command = N'INSERT #modules SELECT N''?'', definition, [object_id] FROM ?.sys.sql_modules'

SELECT p.[db_name], p.proc_name FROM #procs p

INNER JOIN #modules m

ON p.[db_name] = m.[db_name] AND p.[object_id] = m.[object_id]

WHERE m.definition LIKE '%EXEC (%';

DROP TABLE #procs;

DROP TABLE #modules;

October 31, 2011 9:46 AM
 

Liam North said:

Not sure why the formatting didn't work on my previous post - it looked fine in SSMS!

October 31, 2011 9:47 AM
 

AaronBertrand said:

Liam, I'm not sure I understand. Do you have procedure names that are longer than 128 characters? Perhaps you should be using nvarchar for your column data types instead of varchar?

October 31, 2011 10:40 AM
 

Liam North said:

Aaron, no I don't (although some are pretty long - not my doing!), but when executing the query it gave this error for each database:

Msg 103, Level 15, State 4, Line 3

The identifier that starts with '[Administration].sys.procedures AS p

            INNER JOIN [[Administration].sys.sql_modules AS m

            ON p.[obj' is too long. Maximum length is 128.

Msg 102, Level 15, State 1, Line 7

Incorrect syntax near '='.

Have you tried running the query yourself?

Anyway, you pointed me in the right direction with sys.sql_modules so thanks very much for that.

October 31, 2011 12:51 PM
 

Liam North said:

Sorry, I should have replaced [Administration} with a generic [database_name] in the previous post.

October 31, 2011 12:52 PM
 

Andrew Jessop said:

I too have had the problem with sp_msforeachdb where it just stops early, without processing all of the databases

I use the command to retrieve the results from sys.dm_db_index_physical_stats and store the results in my own database to analyse the index fragmentation in each database, but when run on the SQL server with the largest number of databases it occasionally cuts out and just stops after 30 or 40 databases, after 10 minutes or so. No error message.

I used Aarons tips to write my own version (very much cut-down):

BEGIN

SET NOCOUNT ON;

DECLARE @dbname nvarchar(100);

DECLARE @sqlcmd nvarchar(1000);

DECLARE DBs CURSOR STATIC FOR

/* define HERE the list of databases to be processed */

SELECT name FROM sys.databases WHERE name NOT IN ('TempDB','Model') ORDER By name;

OPEN DBs;

FETCH NEXT FROM DBs INTO @dbname;

WHILE @@FETCH_STATUS = 0

BEGIN

/* Write a procedure to do the work on each database */

SET @sqlcmd = 'DBadministration.dbo.dba_fragmentation ' + QUOTENAME(@dbname);

PRINT @sqlcmd;

EXECUTE sp_executesql @sqlcmd;

FETCH NEXT FROM DBs INTO @dbname;

PRINT 'Next: ' + CONVERT(NVARCHAR(20),GETDATE(),120) + ', ' + @dbname + ', Fetch Status ' + CAST(@@FETCH_STATUS as NVARCHAR(4))

   PRINT '-----------------------------------------------------------------';

END

CLOSE DBs;

DEALLOCATE DBs;

PRINT '=================================================================';

END

dba_fragmentation is my stored procedure to process the results of sys.dm_db_index_physical_stats

The PRINT commands are diagnostics so I can see what's happening

The STATIC in DECLARE DBs CURSOR  seems to be the essential part

Without it the process is prone to fail in the same way that sp_msforeachDB does, and when it fails it returns @@FETCH_STATUS = -2 on the last database.

That means that the record it is trying to retrieve next has been deleted from the record set. As the databases are clearly not being deleted, I am wondering if any change to the sys.databases record is reflected in the results set by an apparent deletion. There are a number of dynamic fields in sys.databases such as log_reuse_wait, and is it possible that a change to one of these values during the course of the procedure would make the sys.databases record 'disappear'?

Anyway, putting STATIC in (as it is in Aaron's script) puts the databases list into tempdb and then retrieves it from there where it is not changed by anything that happens in sys.databases

Lo and behold, when I put PRINT @@FETCH_STATUS at the end of the original procedure running sp_msforeachDB, it returns -1 (end of set) when it does work and -2 when it does not. Looks like sp_msforeachdb doesn't have a STATIC in its cursor

March 27, 2012 10:17 AM
 

Aaron Bertrand said:

I've filed a few Connect items recently that I think are important. In #752210 , I complain that the

July 6, 2012 10:51 AM
 

Artur Fonseca said:

I change database "master" to one database current and i works.

August 13, 2012 9:55 AM
 

Justin Larson said:

Andrew Jessop, you're my hero.

July 12, 2013 2:44 PM
 

Brian said:

Thank you very much for this.

We have an Agent job that must run hourly against multiple databases to support an application. It had been fine without any (known) issues for well over two years, but in the last two weeks it has randomly failed to process several databases each day. I thought that I was going crazy until I read your post and realized that other people were experimenting the same issues.

This should be fixed or dropped. It is a potential issue waiting to happen. I've since switched over to your SP and everything seems fine again so far.

October 30, 2013 5:03 AM
 

Brian said:

Has anyone seen sp_MSforeachdb consistently miss a database? We have seen some of our monitoring code (using other methods) miss a database but if come back for a second try right away finds it, like the database status is fleetingly inaccessable.

October 30, 2013 3:51 PM
 

Runa said:

Hello, I recently started to use this SP.

However, i am seeing the error message thrown out.

Maximum length is 128

I quoted with single..  i am nor sure why is it showing the error.

thanks

February 7, 2014 12:16 PM
 

AaronBertrand said:

Runa, do you have a database name with a length approaching 128 characters? Otherwise, I can't really troubleshoot your code if I can't *see* your code.

February 7, 2014 12:31 PM
 

James Watson said:

I just ran into this same issue and through further testing it appears that the databases I was missing were those that I had no permissions to.  I had permissions into most all of the databases on the server and didn't notice that the ones missing might actually be related.  Aaron I've started using code with a few minor changes, so I don't have to wonder what's happening under the hood anymore.  Great post.

April 3, 2014 1:11 PM
 

John said:

Your SP uses an EXEC within it so when I try to insert the results into a table I get the error "An INSERT Exec statement cannot be nested." Is there any way around this while using your foreach proc?

Example:

DECLARE @dbName Varchar(100), @SQL NVARCHAR(1000)

DECLARE @DBInfo TABLE  

(DatabaseName VARCHAR(100));

SELECT @SQL = 'USE ?; SELECT ''?'' AS DatabaseName'

INSERT INTO @DBInfo  

  (DatabaseName)

EXEC usp_dba_foreachdb @SQL  

SELECT * FROM @DBInfo

May 9, 2014 4:32 PM
 

wqweto said:

@John: Try this modified version of sp_foreachdb that does not use INSERT/EXEC

https://gist.github.com/wqweto/7d87441280e57a948807

December 19, 2014 3:37 PM
 

tobi said:

The source code shows a global cursor. Does this not mean that concurrent executions are trampling on each others database list? This might cause missed databases as well as duplicates.

March 30, 2016 9:23 AM
 

AaronBertrand said:

@tobi no, global in this sense does not quite mean the same thing as global temp tables, it is "global" to the connection, not to the instance. Local means that it is only in the current scope. So if a stored procedure (a) calls another stored procedure (b) that has a cursor, if that cursor is local, only b can see it, but if it is global, a can see it too. But only within the same connection - if two concurrent sessions are running the same code, they will each have their own copy of the cursor, whether it is local or global. The naming choice was poor and ambiguous IMHO - should have been something like MODULE_SCOPE or SESSION_SCOPE.

The problem, I believe, is that the default is susceptible to skipping any rows that are "touched" during execution, which means any status change to a database (even if it doesn't actually change a value, obtaining a lock on a row could affect this) - most things don't change frequently, like whether the db is trustworthy, but other things do depending on manual changes like maintenance or bulk operations (recovery model), code testing (compat level), and changes that require single_user. And then of course other things that change frequently on their own, like log_reuse_wait_desc.

And while that is just a theory, I have never been able to reproduce the problem with my own cursor overriding the defaults, even on the same system under the same workload when the skipping was observed frequently with the system procedure.

March 30, 2016 9:40 AM
 

Brad W said:

I'm trying to parameterize this approach. The user should be able to pass in a parameter, like the name of the database, and I run this routine using that data. The specific case where I want to use it is to create database snapshots. This particular DB has 30 filegroups and users shouldn't have to know all the internal details to create and use snapshots.

As a sample, this is the SQL I'm trying to parameterize:

EXECUTE sp_MSForEachDB  

'N(''IF ''?'' = ''master''

BEGIN

USE ?;

SELECT DB_NAME() from sys.tables;

END''); '

My attempt looks like this:

DECLARE @db AS NVARCHAR(100);

DECLARE @SQLQuery AS NVARCHAR(100);

DECLARE @ParameterDefinition AS NVARCHAR(100);

/* set the parameter value */

SET @db = 'FitnesseTest';

/* Build Transact-SQL String by including the parameter */

SET @SQLQuery = 'EXECUTE  

N''IF ''?'' = ''@db1''

BEGIN

USE ?;

SELECT DB_NAME() from sys.tables;

END'';'

/* Specify Parameter Format */

SET @ParameterDefinition =  '@db1 NVARCHAR(100)';

/* Execute Transact-SQL String */

EXECUTE sp_MSForEachDB @SQLQuery, @ParameterDefinition, @db;

I've tried every combination of 2, 3, 4, and 5 quotes to properly escape the single quote and I just can't find the right combination.

April 12, 2016 6:01 PM
 

Brad W said:

I'm trying to parameterize this approach. The user should be able to pass in a parameter, like the name of the database, and I run this routine using that data. The specific case where I want to use it is to create database snapshots. This particular DB has 30 filegroups and users shouldn't have to know all the internal details to create and use snapshots.

As a sample, this is the SQL I'm trying to parameterize:

EXECUTE sp_MSForEachDB  

'N(''IF ''?'' = ''master''

BEGIN

USE ?;

SELECT DB_NAME() from sys.tables;

END''); '

My attempt looks like this:

DECLARE @db AS NVARCHAR(100);

DECLARE @SQLQuery AS NVARCHAR(100);

DECLARE @ParameterDefinition AS NVARCHAR(100);

/* set the parameter value */

SET @db = 'FitnesseTest';

/* Build Transact-SQL String by including the parameter */

SET @SQLQuery = 'EXECUTE  

N''IF ''?'' = ''@db1''

BEGIN

USE ?;

SELECT DB_NAME() from sys.tables;

END'';'

/* Specify Parameter Format */

SET @ParameterDefinition =  '@db1 NVARCHAR(100)';

/* Execute Transact-SQL String */

EXECUTE sp_MSForEachDB @SQLQuery, @ParameterDefinition, @db;

I've tried every combination of 2, 3, 4, and 5 quotes to properly escape the single inner quote and I just can't find the right combination.

April 14, 2016 12:28 PM
 

Roy Kimball said:

Hi Aaron, I've got database names that look as follows:

dbo.Trek.Turner.Trio

dbo.Flit.FishPro.Flint

dbo.Client.Report

How can I modify the procedure you have given to fit to my

needs. Please give me a direction of what approach I can take.

Your time is much appreciated.

Thanks a lot

Roy

August 30, 2016 8:13 AM
 

AaronBertrand said:

Roy, can you explain the problem? The only database names that should cause problems are ones that contain apostrophes. Have you tried it? What was the result?

October 6, 2016 2:29 PM

Leave a Comment

(required) 
(required) 
Submit

About AaronBertrand

...about me...

This Blog

Syndication

Archives

Privacy Statement