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


Example usage:
spu_scriptprocedures @db='master', @proceduresToScriptCondition='ROUTINE_NAME = ''spu_compareprocedures'''

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.