Making a more reliable and flexible sp_MSforeachdb

By:   |   Comments (52)   |   Related: 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. 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. I first blogged about it back in 2010, then again when this tip was published. In 2016, it was added to Brent Ozar Unlimited's SQL Server First Responder Kit, where community members help contribute fixes and enhancements.

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:

database list

(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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Monday, August 17, 2020 - 9:57:55 AM - Aaron Bertrand Back To Top (86312)
Clare, the procedure was added to the open source First Responder Kit, and exclude_list was added there (along with other changes after this article was written).

sp_foreachdb has been deprecated (see https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/pull/2123) and completely replaced by a superior version called sp_ineachdb, which I recommend you use instead. But the First Responder Kit is still going to be the best place to get the code.
https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/blob/dev/sp_ineachdb.sql

Sunday, August 16, 2020 - 8:25:29 PM - Clare Parkinson Back To Top (86311)
I have an old school version of this proc which still uses the @exclude_list parameter. Is there a reason it was removed from the current version?

Wednesday, May 6, 2020 - 7:29:02 AM - Walter Back To Top (85581)

Just a drive by note, and not clear whether the sp posted above is up to date with latest or not (replaced by sp_ineachdb? not sure), but for whatever reason the version of this SP as posted on this page does not work (against my local Dev SQL server - 13.0.50226.0), it returns null for Databasename in for every database for the following query:

sp_foreachdb  'SELECT DB_NAME(DB_ID(''?'')) AS DatabaseName'

(The same but using the MS version works as expected.)


Thursday, May 23, 2019 - 10:20:24 AM - Aaron Bertrand Back To Top (80170)

@Joyce yes, it's called sp_ineachdb and it's in the first responder kit:

https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit

And posted here:

https://www.mssqltips.com/sqlservertip/5694/execute-a-command-in-the-context-of-each-database-in-sql-server--part-2/

https://www.mssqltips.com/sqlservertip/5686/execute-a-command-in-the-context-of-each-database-in-sql-server-using-spineachdb/


Thursday, May 23, 2019 - 9:48:56 AM - Joyce Block Back To Top (80169)

Hi Aaron,

Have you completed the new stored procedure mentioned in your reply to Santhosh on 9/8/18?

"Santhosh, I know it's been a long time, but I have a new stored procedure that will be posted here soon that will resolve that problem. The issue is that this procedure uses INSERT ... EXEC sys.sp_executesql, and the nested insert is not allowed. The new variation gets around this problem by approaching the gathering of the databases in a different way."


Monday, May 13, 2019 - 6:49:44 PM - John Nelson #2 Back To Top (80062)

 Is there also a replacement for sp_msforeachtable somewhere?


Tuesday, April 23, 2019 - 8:32:06 PM - Aaron Bertrand Back To Top (79670)

@JM these are hosted here:

https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit


Tuesday, April 23, 2019 - 4:24:41 PM - JM Back To Top (79667)

Hi Aaron,

Do you happen to have the newest version of this or the Github link to the script?


Saturday, September 8, 2018 - 12:25:30 AM - Aaron Bertrand Back To Top (77397)

Santhosh, I know it's been a long time, but I have a new stored procedure that will be posted here soon that will resolve that problem. The issue is that this procedure uses INSERT ... EXEC sys.sp_executesql, and the nested insert is not allowed. The new variation gets around this problem by approaching the gathering of the databases in a different way.


Saturday, September 8, 2018 - 12:23:43 AM - Aaron Bertrand Back To Top (77396)

 Diane, sorry about the delay, I tried to reproduce your issue but could not. Could you provide some more details?


Friday, January 19, 2018 - 3:39:04 PM - Diane Back To Top (74995)

I am on SQL Server 2012 and the print_only does not work? I tried it on SQL 2008 R2 and it works fine

 

EXEC sp_foreachdb

 

@command = N'RESTORE DATABASE [?]

 

 

 

FROM DISK=''M:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\?_backup.bak''

WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5;',

 

@print_command_only = 1,

 

@user_only = 1,

 

@recovery_model_desc = N'SIMPLE',

 

@suppress_quotename = 1;

 

 


Friday, November 4, 2016 - 9:09:43 AM - Santhosh Back To Top (43702)

I am trying to run certain code to collect some info.

When I use this procedure, I get 1 grid per database.

Is there any way to combine all database results into single one (list all results in 1 grid)

 

I tried the following code and got the error:

DECLARE @temp TABLE

        (

         ServerName VARCHAR(200)

        , DBName VARCHAR(200)

        , ProcedureName VARCHAR(200)

        )

 

INSERT @temp

EXEC sp_foreachdb '

 

SELECT @@SERVERNAME AS ServerName, ''?'' AS DBName, so.name AS ProcedureName

FROM sys.sql_modules AS sm JOIN sys.objects AS so ON sm.object_id = so.object_id  

'

 

SELECT * FROM @temp

ORDER BY DBName, ProcedureName

 

 

Msg 8164, Level 16, State 1, Procedure sp_foreachdb, Line 86

An INSERT EXEC statement cannot be nested.

 


Thursday, October 13, 2016 - 2:33:21 PM - Larry Silverman Back To Top (43558)

We've been using your script from the start, so I'm not surprised we had an ancient version before I updated it this month.

Yes, I've tried using the actual login. I actually get different results running 'SELECT name FROM sys.databases;' when logged in as the user. I get all the databases I'm expecting, all those the user has access to. Still logged in as this user, when I do the EXEC AS USER version, I get just 3 databases back. So these must not evaluate as equivalent statements.

I'm trying to think of any other useful info to provide. I've worked around the issue at this point, so please don't feel like I'm waiting for a fix from you. I thought you'd want to know that there might be something funky going on with non-admin users. I'm happy to try any other suggestions you might have, but it's not urgent.

Thanks.


Thursday, October 13, 2016 - 2:30:00 PM - Aaron Bertrand Back To Top (43557)

Sorry, I forgot that there was a minor update to the code in April, but this should not affect the list of databases you get. What I did observe is that if you execute as the login instead of the user, you get different results (one of the subtleties of using EXECUTE AS):

EXEC('SELECT name FROM sys.databases;') AS USER = N'florb'; -- limited set of databases

EXEC('SELECT name FROM sys.databases;') AS LOGIN = N'florb'; -- all databases

 


Thursday, October 13, 2016 - 1:03:16 PM - Aaron Bertrand Back To Top (43556)

Larry, are you only testing this using EXECUTE AS? Have you tried logging in as the actual login? Also, what do you mean by "latest version"? This code hasn't changed in 6 years...


Thursday, October 13, 2016 - 12:32:03 PM - Larry Silverman Back To Top (43555)

Your answer makes sense, but it doesn't explain what I'm seeing. The query I'm using worked fine for a couple of years. It stopped working when I updated to your latest version of the sproc. Now, I get about 4 of the databases of interest out of >100. The user has permissions in all the databases.

Running your sys.databases query as the user returns master, tempdb and whatever database I'm currently inside of. It doesn't return databases that the user has permissions within.


Tuesday, October 11, 2016 - 3:11:20 PM - Aaron Bertrand Back To Top (43541)

 

Larry, that's right, a lower-privileged user does not automatically get visibility into all of the databases on the system, and can't execute arbitrary commands against databases he/she doesn't have access to. This is by design. What you're seeing in your output has nothing at all to do with FOR XML PATH or my procedure in general - it is simply that the user can't see all of the rows in sys.databases. Try this code for example, and compare the output to you running as sa:

 

USE master;
GO

CREATE LOGIN blat WITH PASSWORD = 'x', CHECK_POLICY = OFF;

CREATE USER blat FROM LOGIN blat;

GO

SELECT name FROM sys.databases; --> full list if running as sa

EXEC (N'SELECT name FROM sys.databases;') AS USER = N'blat'; --> probably just master/tempdb


Tuesday, October 11, 2016 - 1:44:30 PM - Larry Silverman Back To Top (43540)

Hi again!

Having a weird issue which I cannot figure out. When running as a minimally privileged domain user, I'm hitting some kind of length restriction when passing the @database_list through the FOR XML PATH portion of the logic. If I run as sa, I get the full list of databases. As a low priv user, I get truncated to 4 databases.

I'm running the query through powershell and SSMS, and I get truncated in both cases when using the low priv user.

The ROW_NUMBER() bit works fine, generates the full number list for both users. It's definitely something in the SELECT DISTINCT ... FOR XML PATH that's blowing up. I've PRINT-debugged it to this segment of code.

 

Ideas?

Thanks.


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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 6, 2013 - 9:35:34 AM - noname Back To Top (26648)

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 5, 2013 - 7:07:20 PM - Aaron Bertrand Back To Top (26632)

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 5, 2013 - 1:16:25 PM - noname Back To Top (26621)

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

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

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

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

@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 (19948)

*** 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 6, 2012 - 4:09:30 PM - Larry Silverman Back To Top (18377)

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

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

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


Wednesday, February 9, 2011 - 5:30:26 PM - Adam Gojdas Back To Top (12897)

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 9, 2011 - 5:08:59 PM - Oliver Holloway Back To Top (12896)

@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 9, 2011 - 10:52:00 AM - Oliver Holloway Back To Top (12885)

@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 9, 2011 - 10:32:44 AM - Oliver Holloway Back To Top (12884)

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

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


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

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

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

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

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.

 















get free sql tips
agree to terms