Create a gist now

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Replacement for sp_msforeachdb
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment