Learn more about SQL Server tools

   
   




































Latest from MSSQLTips














Making a more reliable and flexible sp_MSforeachdb

MSSQLTips author Aaron Bertrand By:   |   Read Comments (23)   |   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,'',','),
ááááááááááá
'',''),'','')
ááááááá
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: 12/29/2010





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


print tip Print  
Become a paid author


Learn more about SQL Server tools








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

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Monday, May 18, 2015 - 7:24:57 PM - Et Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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


Wednesday, May 29, 2013 - 10:14:06 PM - Jesse Taber Read The Tip

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 Read The Tip

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 Read The Tip

@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 Read The Tip

*** 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 Read The Tip

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 Read The Tip

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 Read The Tip

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


Wednesday, February 09, 2011 - 5:30:26 PM - Adam Gojdas Read The Tip

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 Read The Tip

@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 Read The Tip

@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 Read The Tip

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 Read The Tip

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


Tuesday, January 18, 2011 - 12:24:09 PM - Howard Read The Tip

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 Read The Tip

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


Wednesday, December 29, 2010 - 10:13:49 AM - johns Read The Tip

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 Read The Tip

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.

 




 
More SQL Server Solutions



 

Follow

Get Free SQL Tips

Twitter

LinkedIn

Google+

Facebook

Pinterest

RSS

Learning

DBAs

Developers

BI Professionals

Careers

Q and A

Today's Tip

Resources

Tutorials

Webcasts

Whitepapers

Tools

Search

Tip Categories

Search By TipID

Authors

Community

First Timer?

Pictures

Free T-shirt

Contribute

Events

User Groups

Author of the Year

More Info

Join

About

Copyright

Privacy

Disclaimer

Feedback

Advertise

Copyright (c) 2006-2015 Edgewood Solutions, LLC All rights reserved
Some names and products listed are the registered trademarks of their respective owners.