Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin.
Search for scripts directly from SSMS, and instantly access any saved scripts in your
SSC briefcase from the favorites tab.
Download now (direct download link)
Thank this author by sharing:
By Partha Pal,
This document describes how to generate script for multiple stored procedures ('Create' or 'Alter') with the help of a generalized stored procedure.
Script generation narration :In an existing large production environment it often happens that there are multiple users exist and they have different access permission on existing procedres. Now if we require to do some changes in those procedures, we need to keep the existing permisions intact. In this scenario we need to pass the 'Alter' script instead of 'Drop' & 'Create' script. We may also need to generate create script for new procedures.
There are many situations when such requirements come to us.
Let's take one example-
We are working on a development or an enhancement ticket where we are required to modify many existing stored procedures and also create some number of them. After the job is done we need to send a consolidated script of the changes to be run in the uat, production environment.
Normally it is a manual affair of selecting the stored procedures and create either ALTER or CREATE scripts depending upon whether the stored procedure is modified or new respectively. In this scenario my new generalized stored procedure will come handy as you will have to pass only the name of the stored procedures and identifier to notify if they are 'N'ew or 'O'ld.
To use this script
1. Run the above stored procedure in your sql server database.
2. Please select 'Results to Text' or 'Results to File' option in Query analyzer.
3. After completing step 1,2 execute the procedure as shown below:
Exec uspScriptGenerator 'Proc1~N|Proc2~O|' or 'Proc1~N|Proc2~O'
Note: 'N' is used to notify it's 'New' hence it will be 'Create' script.'O' is used to notify it's 'Old' (existing) hence it will be 'Alter' script.
The consolidated script will generate like
/*** Start of script ***/GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
Create Procesure Proc1 ...
GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO /*** End of script ***/
Alter Procesure Proc2 ...
This Script generates drop and create scripts for views or stored procedures.
Scripting the IF EXISTS portion of a DROP statement for Stored Procedures
Scripting Stored Procedures
Creating view from stored procedure results
how to customize SMO script for ALTER or CREATE stored procedure