Overview:
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 ***/
/*** Start of script ***/
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Alter Procesure Proc2 ...
.
.
.
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
/*** End of script ***/
