I created my sp_ForEachProcedure base on Microsoft undocumented sp_msForEachTable. It doesn’t have all the features of sp_msForEachTable, but does include a few of them. I even added a few custom parameters (@print, @execute). I did dynamic SQL because I was having problems creating the where statement. I believe I could use a case, but went ahead and used the dynamic sql. I would like to join to syscomments and search the actual stored procedure, but ran out of time.
In the future I plan on creating sp_ForEachView
–example of sp_msforeachtable
–exec sp_msforeachtable @command1=’PRINT ”?”’, @whereand=’ and o.name like ”%Human%”’
ALTER
PROCEDURE sp_ForEachProcedure
@command1
VARCHAR(MAX),
@whereand
VARCHAR(MAX) = NULL,
@print
BIT = 1,
@execute
BIT = 0
AS
–DECLARE @command1 VARCHAR(MAX)
–DECLARE @whereand VARCHAR(MAX)
–SET @command1 = ‘PRINT ”?”’
–SET @whereand = ‘name like ”%contact%”’
DECLARE
@executeCommand NVARCHAR(MAX)
SET
@executeCommand =
‘ DECLARE @statementTable TABLE (ID INT IDENTITY(1,1) ,statement NVARCHAR(MAX)) ‘
+
‘ DECLARE @userCommand NVARCHAR(MAX) ‘
+
‘ INSERT INTO @statementTable (statement) ‘
+
‘ SELECT ‘
+
‘ REPLACE(”’
+ REPLACE(@command1, ””, ”””) + ”’, ”?”, ”[” + p.name + ”]”)’ + ‘ AS Statement’ +
‘ FROM SYS.Procedures p ‘
IF
(@whereand IS NOT NULL)
BEGIN
SET @executeCommand = @executeCommand +
‘ WHERE ‘
+ REPLACE(@whereand, ”, ””)
END
IF
(@print = 1)
BEGIN
SET @executeCommand = @executeCommand + ‘ SELECT * FROM @statementTable’
END
IF
@execute = 1
BEGIN
SET @executeCommand = @executeCommand +
‘ DECLARE @loopCount INT ‘ +
‘ DECLARE @statement NVARCHAR(MAX) ‘ +
‘ SELECT @loopCount = COUNT(*)FROM @statementTable ‘ +
‘ WHILE @loopCount <> 0 ‘ +
‘ BEGIN ‘ +
‘ SET @statement = (SELECT statement FROM @statementTable WHERE ID = @loopCount) ‘ +
–‘ PRINT ”EXECUTING: ” + @Statement ‘ +
‘ EXEC sp_executesql @Statement ‘ +
‘ SET @loopCount = @loopCount – 1 ‘ +
‘ END ‘
END
–FOR DEBUGGING
–PRINT @executeCommand
EXEC
sp_executesql @executeCommand
GO
–sp_ForEachProcedure ‘PRINT ”?”’, ‘NAME LIKE ”%Human%”’, 1
–sp_ForEachProcedure ‘GRANT EXECUTE ON ? TO Scrub ‘, ‘NAME LIKE ”%Human%”’, 1, 1
References:
Is a Temporary Table Really Necessary (How not to use cursors):
