SQL Server - ForEachRoutine Script

Yet another SQL Script you might find useful.
This one I store in the master database so I have sp_ as the prefix. Normally my standard for stored procedures would be to prefix them with proc_.
This one I use to mimic the sp_MSForEachTable proc but this one is to manage permission on stored procedures within any database.
As always... I hope you find it of as much use as I do. I use it daily!


/*******************************************************************************************************
* master.dbo.sp_AOCForEachRoutine
* Creator: Merrill Nelson
* mnelson@courts.az.gov
*
* Description: Execute a Command on Procedures and/or Functions
* Notes: Strings/Parameters: @Action - (ie: 'grant exec')
* ,@Role - Database Role to Apply to
* ,@Filter1 - (ie: 'proc_%')
* ,@Filter2 - (ie: 'func_%')
*
* Usage:
* exec sp_AOCForEachRoutine 'grant execute', 'Developer', 'proc_%'
* exec sp_AOCForEachRoutine 'grant execute', 'Developer', 'func_%'
* exec sp_AOCForEachRoutine 'grant view definition', 'Developer', 'proc_%', 'func_%'
*
* Modifications:
* Developer Name Date Brief Description
* ------------------ -------- ------------------------------------------------------------
* Merrill Nelson 07/07/06 Original Creation
********************************************************************************************************/
CREATE Procedure [dbo].[sp_AOCForEachRoutine]
@Action varchar(25)
,@Role varchar(15)
,@Filter1 varchar(10) = ''
,@Filter2 varchar(10) = ''
As
-- Create Temp Table to hold Routine name info
Create table #Routines ( [ID] int identity(1,1), Routine_Schema varchar(20), Routine_Name varchar(75) )
-- Setup local variables
Declare @Count int
,@SQL varchar(200)
,@Schema varchar(20)
,@RoutineName varchar(75)
,@MySQL varchar(1000)
-- Setup SQL to load temp table
Set @MySQL = '
Select Routine_Schema, Routine_Name
From ' + db_name() + '.information_Schema.routines
Where Routine_Name like ''' + @Filter1 + '''
Or Routine_Name like ''' + @Filter2 + ''' '
-- Load Temp Table
Insert into #Routines
Exec(@MySQL)
-- Set Counter variable to greatest [ID] from Temp Table
Set @Count = ( Select max([ID]) From #Routines )
-- While Counter is > 0
While @Count > 0
Begin
-- Get Schema Name from Temp Table
Set @Schema = ( Select Routine_Schema From #Routines Where [ID] = @Count )
-- Get Routine Name from Temp Table
Set @RoutineName = ( Select Routine_Name From #Routines Where [ID] = @Count )
-- Set SQL statement to run using current db name, schema, routine, and role
Set @SQL = @Action + ' on ' + db_name() + '.' + @Schema + '.' + @RoutineName + ' to ' + @Role
-- Print command to be run
Print @Action + ' on ' + db_name() + '.' + @Schema + '.' + @RoutineName + ' to ' + @Role
-- Execute Command
Exec(@SQL)
-- Decrement Counter variable
Set @Count = @Count -1
-- Loop
End
Drop table #Routines

Regards,
-Merrill