Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
dba_ForEachDB.sql #blog
-- http://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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.