A better sp_MSForEachDB
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.
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:
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:
-- -- 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
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.