A better sp_MSForEachDB

Sep 9

Posted by


Though undocumented and unsupported, I’m sure that at least once you happened to use Microsoft’s built-in stored procedure to execute a statement against all databases. Let’s face it: it comes handy very often, especially for maintenance tasks.

Some months ago, Aaron Bertand (blog|twitter) came up with a nice replacement and I thought it would be fun to code my own.

The main difference with his (and Microsoft’s) implementation is the absence of a cursor. While flagged correctly (LOCAL FORWARD_ONLY STATIC READ_ONLY) and run against a temporary table, nevertheless I was a bit disturbed by that tiny little cursor, so I decided to get rid of it.

Basically, my code relies on a dynamic SQL pushed down three levels:

  1. sp_executesql
  2. sp_executesql
  3. EXEC

This trick can be used as many times as you like, given that you keep on declaring and passing all the parameters you need to the lower levels.

I didn’t provide ad-hoc parameters to implement complex filters on sysdatabases, as I’m convinced that they would not be useful enough in a day to day use. If you like this code and want to use it, feel free to change it to incorporate any kind of filter.

Here is the code:

-- https://spaghettidba.com/2011/09/09/a-better-sp_msforeachdb/
--
-- Author: Gianluca Sartori @spaghettidba
-- Date: 2011/09/09
--
-- Description: Executes a statement against multiple databases
-- Parameters:
-- @statement: The statement to execute
-- @replacechar: The character to replace with the database name
-- @name_pattern: The pattern to select the databases
-- It can be:
-- * NULL - Returns all databases
-- * [USER] - Returns users databases only
-- * [SYSTEM] - Returns system databases only
-- * A pattern to use in a LIKE predicate against the database name
CREATE PROCEDURE [dba_ForEachDB]
@statement nvarchar(max),
@replacechar nchar(1) = N'?',
@name_pattern nvarchar(500) = NULL
AS
BEGIN
SET NOCOUNT ON
DECLARE @sql nvarchar(max)
-- LEVEL 3:
-- Build an intermediate statement that replaces the '?' char
SET @sql = 'SET @statement = REPLACE(@statement,'''+ @replacechar +''',DB_NAME()); EXEC(@statement);'
SET @sql = REPLACE(@sql, '''', '''''')
SET @sql = 'N''' + @sql + ''''
-- LEVEL 2:
-- Build a statement to execute on each database context
;WITH dbs AS (
SELECT *,
system_db = CASE WHEN name IN ('master','model','msdb','tempdb') THEN 1 ELSE 0 END
FROM sys.databases
WHERE DATABASEPROPERTY(name, 'IsSingleUser') = 0
AND HAS_DBACCESS(name) = 1
AND state_desc = 'ONLINE'
)
SELECT @sql = (
SELECT
'EXEC ' + QUOTENAME(name) + '.sys.sp_executesql ' +
@sql + ',' +
'N''@statement nvarchar(max)'',' +
'@statement;' AS [text()]
FROM dbs
WHERE 1 =
CASE
-- No filter? Return all databases
WHEN @name_pattern IS NULL THEN 1
-- User databases
WHEN @name_pattern = '[USER]' THEN system_db + 1
-- System databases
WHEN @name_pattern = '[SYSTEM]' THEN system_db
-- LIKE filter
WHEN name LIKE @name_pattern THEN 1
END
ORDER BY name
FOR XML PATH('')
)
-- LEVEL 1:
-- Execute multi-db sql and pass in the actual statement
EXEC sp_executeSQL @sql, N'@statement nvarchar(max)', @statement
END
view raw dba_ForEachDB.sql hosted with ❤ by GitHub

Let’s see some examples of its use:
Print the database name for each user database:

EXEC [dba_ForEachDB] @statement = 'PRINT DB_NAME()', @replacechar = '?', @name_pattern =  '[USER]'

Display the file path of each database file of system databases:

EXEC [dba_ForEachDB] @statement = 'SELECT physical_name, size FROM sys.database_files', @replacechar = '?', @name_pattern =  '[SYSTEM]'

I hope you like it and find it useful.
Happy coding.

Posted on September 9, 2011, in SQL Server, T-SQL and tagged , . Bookmark the permalink. 12 Comments.

  1. Jason Brimhall (@sqlrnnr) | December 5, 2011 at 6:24 pm

    Gianluca – thanks for the script. When looking at trying to not use MSforeachdb recently I was going to venture down this same path. You just saved me a bunch of work.

    Reply
  2. spaghettidba | December 5, 2011 at 6:27 pm

    Thanks! Glad you liked it, Jason.

    Reply
  3. Erez | August 22, 2013 at 8:49 pm

    This works for me. thanks a lot !
    Erez

    Reply
  4. Anon | February 17, 2014 at 7:56 am

    Thanks, but I got some problem. I don´t think it handles dbname with multiple – in the name.
    I got these databases and it failes can´t find X123456.

    X123456-XXXX
    X123456-YYY
    X123456-CashM
    X123456-XXXX-Demo50B
    X123456-YYY-Demo50B
    X123456-XXXX-CUR

    Reply
    • spaghettidba | February 17, 2014 at 9:54 am

      I just tried with the database names you suggested and it works for me.
      Can you provide the whole set of parameters you are using?
      Which version are you running?

      Reply
    • corey Lawson | September 13, 2016 at 12:02 am

      put square brackets around the string, or use QUOTENAME(‘[‘, objname), for building up your SQL string.

      Reply
      • corey Lawson | September 13, 2016 at 12:04 am

        put square brackets around the parts in the SQL string for field names, table names or database names, or use QUOTENAME(‘[‘, objname), for building up your SQL string

        as in:

        @sql = ‘select * from [‘ + @dbname + ‘].dbo.[‘ + @tbl + ‘];’

        or

        @sql = ‘select * from ‘ + quotename(‘[‘, @dbname) + ‘.dbo.’+quotename(‘[‘, @tbl)+’;’

  5. mobile | November 20, 2019 at 12:23 am

    In a “It’s better than nothing” mindset we ran a COPY_ONLY backup of every database on our servers in the dark of the early morning. This is not a great plan but it was not for production data either. Now if that has got gotten the ire of folks raised hold onto your hat.

    Reply

  1. Pingback: Replay a T-SQL batch against all databases « spaghettidba

  2. Pingback: A viable alternative to dynamic SQL in administration scripts | spaghettidba

  3. Pingback: The Simplest Alternative to sp_MSforeachdb – Eitan Blumin's Blog

  4. Pingback: Script to find the list of stored procedures in all databases

Leave a comment