The final helper procedure I wrote as part of my recent integration exercise was spu_scriptprocedures. It is a simple stored procedure which builds up a defensive SQL script for one or many stored procedures.It has 4 parameters:
- @db - the name of the database. Mandatory
- @rolename - the name of the role for which a grant execute statement should be prepared. Optional - no grant statement is printed if this isn't specified
- @proceduresToScriptCondition - the condition (based on the INFORMATION_SCHEMA.ROUTINES view) to use to select the procedures. Optional - if not specified all stored procedures will be scripted
- @debug - print out the SQL being generated to help debug the code. Optional - defaults to off
This has been tested on SQL Server 2005.As with my other SQL helper procedures, please leave a comment if you have any suggestions on way to improve it, or bugs to report.Download the script here.
spu_scriptprocedures @db='master', @proceduresToScriptCondition='ROUTINE_NAME = ''spu_compareprocedures'''