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.

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.

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

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

Looking forward to trying this solution.

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

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

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

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

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;

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

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

@ParsedString table (RetStr varchar(80

))

-- Assumes the returned parced value is 80 characters

DECLARE @column varchar(80), @Pos int

SET @List = LTRIM(RTRIM(@List))+ ';'

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

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

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

*** 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

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

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