Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Making a more reliable and flexible sp_MSforeachdb


By:   |   Read Comments (34)   |   Related Tips: 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. (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
;

   
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


Last Update:






About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand is a Senior Consultant at SQL Sentry, Inc., and has been contributing to the community for about two decades, first earning the Microsoft MVP award in 1997.

View all my tips





More SQL Server Solutions




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Tuesday, September 20, 2016 - 3:36:44 PM - Larry Silverman Back To Top

Figured it out. Implicit conversion. I declared an nvarchar(max) but was using SET @sql = '' instead of SET @sql = N''. Plus I had a concatenation as you suggested.

This article helped a lot: https://stackoverflow.com/questions/4833549/nvarcharmax-still-being-truncated

Would be a nice defensive addition to have a safeguard in your script to detect input sql of exactly 4000 or 8000 length, and warn the user they might be suffering from accidental truncation. Usually the script won't run due to cutting a statement in half, but some may get through depending on the boundary. 

Thanks!


Tuesday, September 20, 2016 - 1:52:55 PM - Aaron Bertrand Back To Top

Larry, is it possible your command was truncated before it got passed to sp_foreachdb? I have certainly tested this with commands > 4000 characters. Add a line to the beginning of the procedure:

SELECT LEN(@command), DATALENGTH(@command);

I suspect that you are concatenating strings together and at some point you have a string you think is max but is actually not seen that way by SQL Server.


Tuesday, September 20, 2016 - 12:46:51 PM - Larry Silverman Back To Top

Aaron,

Hit a snag with sp_foreachdb today. My dynamic SQL exceeded 4000 nvarchar. There is a 4000 nvarchar limitation in sp_executesql (at least, in my SQL 2012 instance). It truncated my dynamic SQL at 4000 and executed it. Yikes!

I searched but haven't seen this limitation mentioned anywhere. Thought you'd want to know. Perhaps the script should check the length of the input and warn the caller about the issue, and not execute the SQL. Or perhaps there's some other workaround.


Friday, April 22, 2016 - 1:11:31 PM - Greg Robidoux Back To Top

The code has been updated to reflect the error Aaron pointed out. 

Sorry about the issue.  As Aaron stated, an update to the tip wiped out the < and > so these were treated as HTML tags and therefore not visible.

This should work now.

-Greg


Friday, April 22, 2016 - 11:23:37 AM - mmcdonald Back To Top

 Looks like the error string posting was garbled (it looked proper in the preview)

["SELECT name FROM sys.databases WHERE 1=1 AND name IN (N'master'N'model'N'msdb') AND state_desc = N'ONLINE' AND is_read_only = 0"]

The dblist contains extra tags when I run Aaron's example.

I found http://gruffcode.com/2013/05/29/tweaking-sp_foreachdbs-database_list-parameter-handling/ which helped.  However, I only changed Aaron's top most replace on @dblist

       --SELECT @dblist = REPLACE(REPLACE(REPLACE(x,'',','),'',''),'','')
       SELECT @dblist = REPLACE(REPLACE(REPLACE(x,'',','),'',''),'','')

This resolved it for me.


Friday, April 22, 2016 - 11:18:17 AM - Aaron Bertrand Back To Top

mmcdonald, the error is just in the output to HTML; seems the encoded XML elements were lost in a recent update. This:

       SELECT @dblist REPLACE(REPLACE(REPLACE(x,'',','),

           '',''),'','') 

 

Should be:

       SELECT @dblist REPLACE(REPLACE(REPLACE(x,'</x><x>',','),
           
'</x>',''),'<x>','')

 


Friday, April 22, 2016 - 11:12:09 AM - mmcdonald Back To Top

I found

http://gruffcode.com/2013/05/29/tweaking-sp_foreachdbs-database_list-parameter-handling/

which has the solution for the extra tags


Friday, April 22, 2016 - 10:47:02 AM - mmcdonald Back To Top

It looks like the error is in you dblist creation

 

SELECT name FROM sys.databases WHERE 1=1 AND name IN (N'master'N'model'N'msdb') AND state_desc = N'ONLINE' AND is_read_only = 0

*****This is throwing the error*****


Friday, April 22, 2016 - 10:25:21 AM - mmcdonald Back To Top

Aaron,

Nice rewrite.

However, I receive an error when attempting to use a list of databases (even your example reposted below fails)

EXEC sp_foreachdb
       @command = N'SELECT N''?'', MAX(create_date) FROM ?.sys.objects;',
       @database_list = 'master,model,msdb';

 

Msg 102, Level 15, State 1, Line 13
Incorrect syntax near '<'.

 

Of further interest, using @user_only = 1 does function correctly.

--SQL 2005

 

Thanks

 


Tuesday, April 19, 2016 - 10:03:17 AM - Thomas Pullen Back To Top

Aaron, thank you so much for this. I was tearing my hair out using sp_MSForEachDB on a very busy instance with 1041 databases, it would randomly affect as many of them as it so pleased, e.g. 378, 0 or 973, never the correct number.

Your version got all 1041 of them straight away and repeatedly, thanks a heck of a lot.


Thursday, January 21, 2016 - 10:00:36 AM - Mike Back To Top

So did you solve the problem of skipping databases under heavy load by changing the cursor configuration? I'm doing something very similar to what sp_MSforeachdb does and I'm running into the exact same issue with my code. Only occurs sporadically so hard to trobleshoot and I'm thinking it must be something with the cursor as the rest is super straight forward. Thx for the post, very helpful!


Monday, May 18, 2015 - 7:24:57 PM - Et Back To Top

I tried running this as the command in your foreachDb, and did not have any luck. 

It appears that the function is not run inside the iterator :(

 

SELECT [name], OBJECT_DEFINITION(OBJECT_ID([name])) as sp_content

FROM ?.sys.all_objects 

WHERE OBJECT_DEFINITION(object_id([name])) LIKE '%delete from \[dbo\].\[MySearchTable\]%' ESCAPE '\'


Wednesday, July 30, 2014 - 11:10:41 AM - Jason Back To Top

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)


Wednesday, July 30, 2014 - 11:01:32 AM - Jason Back To Top

This makes the error go away in my 11.0.3368 environment:
EXEC sp_MSforeachdb 'SELECT * FROM [?].sys.objects;'

(Try creating those databases on your system and running EXEC sp_MSforeachdb 'SELECT * FROM ?.sys.objects;'; - you'll get a variety of errors.)

 


Friday, September 06, 2013 - 9:35:34 AM - noname Back To Top

Thanks for that hint Aaron,

No, I didn't actually need to create a table variable, that was just some script I found on the web, which I thought might 'do the job'.

What I actually wanted to do was get a resultset which listed which databases on a SQL Instance had a particular table, in the code below which does just that, the table is named 'InternalVersion'.

 

IF (DB_ID(N'#AllTables') IS NOT NULL)
BEGIN
    ALTER DATABASE [#AllTables]
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [#AllTables];
END

CREATE TABLE #AllTables
(
     DatabaseName NVARCHAR(4000)
    ,TableName NVARCHAR(4000)
);

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
   
DROP TABLE #AllTables;


Thursday, September 05, 2013 - 7:07:20 PM - Aaron Bertrand Back To Top

noname, do you need to use a table variable here? Why not:

 

CREATE TABLE #AllTables(CompleteTableName NVARCHAR(4000));

EXEC usp_foreachdb N'INSERT #AllTables SELECT ...';

SELECT * FROM #AllTables ORDER BY 1;

DROP TABLE #AllTables;


Thursday, September 05, 2013 - 1:16:25 PM - noname Back To Top

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


Wednesday, August 21, 2013 - 10:09:40 AM - Rafael Dontal Gonšalez Back To Top

Thanks, thanks, thanks. Your proc resolve a lot of issues that i was having.


Wednesday, May 29, 2013 - 10:14:06 PM - Jesse Taber Back To Top

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:

http://geekswithblogs.net/GruffCode/archive/2013/05/29/tweaking-sp_foreachdbrsquos-database_list-parameter-handling.aspx


Thursday, October 18, 2012 - 12:40:34 AM - div Back To Top

Thanks Aaron ... i got its solution

 

 DECLARE @SQL AS VARCHAR(1000),

 @command varchar(1000),

 @tablename1 VARCHAR(5000),

 @temp2 varchar(8000),

 @dbname varchar(8000),

@rptdirMaster varchar(1000);

SET @rptdirMaster = 'C:\TEMP$\'

SET @tablename1 = @rptdirMaster + 'QueryTable.csv'

SELECT @command = 'IF ''?'' IN(''brisbane_tsql'', ''sydney'',''perth'',''goldcoast'',''sunshinecoast'') 

                      BEGIN USE ? EXEC(''   SELECT TOP 3 * FROM [?].[DBO].[DOICLAIMDATA];

    '')     

    END' 

    EXEC sp_MSforeachdb  @command

 

  SET @dbname = 'Brisbane_tsql'

 

SET @SQL='SELECT TOP 3 *

 FROM [databasename].[DBO].[DOICLAIMDATA]'

SET @temp2 = 'bcp "'+  REPLACE(REPLACE(replace(@SQL, 'databaseName', @dbname),char(13),' '), CHAR(10), ' ')  +'"  queryout  "D:\DO IT\TEMP\claims_"'+   @dbname   +'".csv" -t, -c -T -SMULTICARD2\SQLEXPRESS'

exec master..xp_cmdshell @temp2

 

Wednesday, October 17, 2012 - 9:13:44 AM - Aaron Bertrand Back To Top

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


Wednesday, October 17, 2012 - 12:09:42 AM - div Back To Top

*** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and past

DECLARE @SQL AS VARCHAR(1000),

 @command varchar(1000),

 @tablename1 VARCHAR(5000),

 @temp2 varchar(8000),

@rptdirMaster varchar(1000);

SET @rptdirMaster = 'C:\TEMP$\'

SET @tablename1 = @rptdirMaster + 'QueryTable.csv'

SELECT @command = 'IF ''?'' IN(''brisbane_tsql'', ''sydney'',''perth'',''goldcoast'',''sunshinecoast'') 

                      BEGIN USE ? EXEC(''   SELECT TOP 3 * FROM [brisbane_tsql].[DBO].[DOICLAIMDATA];

    '')     

    END' 

    EXEC sp_MSforeachdb  @command

 

how can export it to csv


Friday, July 06, 2012 - 4:09:30 PM - Larry Silverman Back To Top

When database replication is enabled, SQL Server 2008 R2 adds a new system database named 'distribution'.  Your script does not account for this new system database.

In order to support the distribution system database, I changed the code like so:

   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) OR is_distributor = 1) ' 
           ELSE '' END
       + CASE WHEN @user_only = 1 THEN 
           ' AND (database_id NOT IN (1,2,3,4) AND is_distributor = 0)' 
           ELSE '' END
 

Tuesday, April 26, 2011 - 3:05:36 PM - Eddy White Back To Top

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.

CODE:

CREATE

 

FUNCTION [dbo].[udfParsedString]

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

(

 

 

@List varchar(8000))

RETURNS

 

 

@ParsedString table (RetStr varchar(80

))

AS

BEGIN

 

 

-- 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 REPLACE(@List, ';', '') <> ''

 

 

-- If there are values in the list, go and process parameters

 

 

BEGIN

 

 

WHILE @Pos > 0

 

 

BEGIN

 

 

SET @column = LTRIM(RTRIM(LEFT(@List, @Pos - 1)))

 

 

IF @column <> ''

 

 

BEGIN

 

 

INSERT INTO @ParsedString (RetStr) VALUES (@column)

 

 

END

 

 

SET @List = RIGHT(@List, LEN(@List) - @Pos)

 

 

SET @Pos = CHARINDEX(';', @List, 1)

 

 

END

 

 

END

 

 

RETURN

END

 

Example call:

 

 

 

 

 

DECLARE @DBExclusionList varchar(2000)

SET @DBExclusionList  = 'column1;column2;column3'

SELECT RetStr FROM dbo.udfMT_ParsedString(@DBExclusionList) ;

Results look as such:

RetStr

column1
column2
column3

 

With it returning a table, you can even use it in inner joins or in statement as such:

 

 

 

 

SELECT 

 

name FROM master.dbo.sysdatabases (NOLOCK)

 

 

WHERE LOWER(name) IN (SELECT LOWER(RetStr)

FROM dbo.udfMT_ParsedString(@DatabaseList) )


Thursday, February 10, 2011 - 3:50:20 PM - Aaron Bertrand Back To Top

Thanks Oliver, errant copy & paste. I'll submit a revision.


Wednesday, February 09, 2011 - 5:30:26 PM - Adam Gojdas Back To Top

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;


Wednesday, February 09, 2011 - 5:08:59 PM - Oliver Holloway Back To Top

@Aaron Bertrand, there seems to be a bit of confusion between the auto_close and auto_shrink lines in the SET @sql statement.


Wednesday, February 09, 2011 - 10:52:00 AM - Oliver Holloway Back To Top

@Tim Plas, this was not too difficult, if you are willing to feed a prepped string. I used a variable named @exclude_list, defaulted to NULL, and added the following in the SET @sql statement:

+ CASE WHEN @exclude_list IS NOT NULL THEN

    ' AND name NOT IN (' + @exclude_list + ')'

    ELSE '' END

I was then able to add this line to the proc call:   @exclude_list = '''master'', ''model''' and received the expected result set.

 

 


Wednesday, February 09, 2011 - 10:32:44 AM - Oliver Holloway Back To Top

Thank you for this replacement proc. I added a variable @affects_tempdb defaulted to 0 to allow proc to be used for backups for all databases without having to work around the tempdb.

+ CASE WHEN @affects_tempdb = 0 THEN

    ' AND name <> ''tempdb'''

    ELSE '' END


Friday, January 28, 2011 - 12:02:18 AM - Adam Back To Top

This was a very nice re-write.  Great new added functionality.


Tuesday, January 18, 2011 - 12:24:09 PM - Howard Back To Top

Thanks for posting.  I've seen numerous times where sp_MSforeachdb skips seemingly random databases.

Looking forward to trying this solution.


Tuesday, January 18, 2011 - 1:13:50 AM - Oliver Holloway Back To Top

Have recently been seeing this in my production systems, thanks for this article!


Wednesday, December 29, 2010 - 10:13:49 AM - johns Back To Top

Aaron,

Thanks for rewriting a very useful procedure. 

I feel free to use it in my production jobs now that it is documentated and maintainable. 

Note - I will change the name to sp_dba_foreachdb.  This naming convention keeps all the procs that I have put into master grouped together and helps avoid naming conflicts.

Thanks again.

 


Wednesday, December 29, 2010 - 10:12:22 AM - Tim Plas Back To Top

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.

 


Learn more about SQL Server tools