Replacement for sp_msforeachdb

sp_foreachdb

USE [master]
GO
IF OBJECT_ID('dbo.sp_foreachdb') IS NULL EXEC ('CREATE PROCEDURE dbo.sp_foreachdb AS RETURN 0')
GO

ALTER PROCEDURE dbo.sp_foreachdb (
			@command             NVARCHAR(MAX),
			@replace_character   NCHAR(1)       = N'?',
			@print_dbname        BIT            = 0,
			@print_command_only  BIT            = 0,
			@suppress_quotename  BIT            = 1,
			@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
/*------------------------------------------------------------------------

	Based on http://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/
	
	2010-12-29: Initial implementation (Aaron Bertrand)
	2014-12-28: Implemented without INSERT/EXEC (wqweto@gmail.com)

*/------------------------------------------------------------------------
SET			NOCOUNT ON

CREATE TABLE #TmpDbList_sp_foreachdb(name SYSNAME)
CREATE TABLE #TmpDbs_sp_foreachdb(name SYSNAME)

DECLARE		@SQL		NVARCHAR(MAX)
			, @CrsDbs	CURSOR
			, @IterName	NVARCHAR(300)

; WITH		n(n) 
AS			(	
			SELECT		ROW_NUMBER() OVER (ORDER BY s1.name) - 1
			FROM		sys.objects AS s1
			CROSS JOIN	sys.objects AS s2	
			)
INSERT		#TmpDbList_sp_foreachdb
SELECT		DISTINCT LTRIM(RTRIM(SUBSTRING(@database_list, n, CHARINDEX(',', @database_list + ',', n) - n))) AS name
FROM		n
WHERE		n <= LEN(@database_list)
			AND SUBSTRING(',' + @database_list, n, 1) = ','

INSERT		#TmpDbs_sp_foreachdb
SELECT		name 
FROM		sys.databases 
WHERE		(database_id IN (1,2,3,4) OR COALESCE(@system_only, 0) <> 1)
			AND (database_id NOT IN (1,2,3,4) OR COALESCE(@user_only, 0) <> 1)
			AND (name LIKE N'%' + @name_pattern + '%' OR @name_pattern = N'%')
			AND (name IN (SELECT name FROM #TmpDbList_sp_foreachdb) OR @database_list IS NULL)
			AND (recovery_model_desc = @recovery_model_desc OR @recovery_model_desc IS NULL)
			AND (compatibility_level = @compatibility_level OR @compatibility_level IS NULL)
			AND (state_desc = @state_desc OR @state_desc IS NULL)
			AND (is_read_only = @is_read_only OR @is_read_only IS NULL)
			AND (is_auto_close_on = @is_auto_close_on OR @is_auto_close_on IS NULL)
			AND (is_auto_shrink_on = @is_auto_shrink_on OR @is_auto_shrink_on IS NULL)
			AND (is_broker_enabled = @is_broker_enabled OR @is_broker_enabled IS NULL)

SET			@CrsDbs = CURSOR FAST_FORWARD FOR 
SELECT		CASE WHEN @suppress_quotename = 1 THEN name ELSE QUOTENAME(name) END 
FROM		#TmpDbs_sp_foreachdb
ORDER BY	name

OPEN		@CrsDbs

WHILE		1=1
BEGIN
			FETCH NEXT 
			FROM		@CrsDbs 
			INTO		@IterName
	
			IF			@@FETCH_STATUS <> 0 BREAK

			SET			@SQL = REPLACE(@command, @replace_character, @IterName)

			IF			@print_command_only = 1
			BEGIN
						PRINT		'/* For ' + @IterName + ': */'
									+ 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		'/* ' + @IterName + ' */'
						END

						EXEC		dbo.sp_executesql @SQL
			END
END

CLOSE		@CrsDbs
DEALLOCATE	@CrsDbs
GO