December 29, 2010 | SQL Server

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:

Bad habits to kick : Relying on undocumented behavior

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.
 

28 comments on this post

    • cinahcaM madA - December 29, 2010, 7:24 PM

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

    • AaronBertrand - December 29, 2010, 11:38 PM

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

    • Rob Farley - December 30, 2010, 5:38 AM

      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.

    • Raul Santos Neto - December 30, 2010, 9:48 PM

      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!

    • Jeff Stanlick - March 28, 2011, 4:19 PM

      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!

    • Liam North - October 28, 2011, 10:19 AM

      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 (%";'

    • AaronBertrand - October 28, 2011, 3:47 PM

      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 (%";';

    • Liam North - October 31, 2011, 4:46 PM

      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;

    • Liam North - October 31, 2011, 4:47 PM

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

    • AaronBertrand - October 31, 2011, 5:40 PM

      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?

    • Liam North - October 31, 2011, 7:51 PM

      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.

    • Liam North - October 31, 2011, 7:52 PM

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

    • Andrew Jessop - March 27, 2012, 5:17 PM

      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

    • Artur Fonseca - August 13, 2012, 4:55 PM

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

    • Justin Larson - July 12, 2013, 9:44 PM

      Andrew Jessop, you're my hero.

    • Brian - October 30, 2013, 12:03 PM

      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.

    • Brian - October 30, 2013, 10:51 PM

      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.

    • Runa - February 7, 2014, 8:16 PM

      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

    • AaronBertrand - February 7, 2014, 8:31 PM

      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.

    • James Watson - April 3, 2014, 8:11 PM

      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.

    • John - May 9, 2014, 11:32 PM

      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

    • wqweto - December 19, 2014, 11:37 PM

      @John: Try this modified version of sp_foreachdb that does not use INSERT/EXEC
      https://gist.github.com/wqweto/7d87441280e57a948807

    • tobi - March 30, 2016, 4:23 PM

      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.

    • AaronBertrand - March 30, 2016, 4:40 PM

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

    • Brad W - April 13, 2016, 1:01 AM

      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.

    • Brad W - April 14, 2016, 7:28 PM

      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.

    • Roy Kimball - August 30, 2016, 3:13 PM

      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

    • AaronBertrand - October 6, 2016, 9:29 PM

      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?

Comments are closed.