Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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)

consolidated script generator for multiple stored procedures

By Partha Pal,

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 ***/

Total article views: 1884 | Views in the last 30 days: 6
 
Related Articles
SCRIPT

Generate Drop / Create Script for views, stored Procedures

This Script generates drop and create scripts for views or stored procedures.

FORUM

Scripting options for Stored Procedures

Scripting the IF EXISTS portion of a DROP statement for Stored Procedures

FORUM

Scripting Stored Procedures

Scripting Stored Procedures

FORUM

Creating view from stored procedure results

Creating view from stored procedure results

FORUM

how to customize SMO script for ALTER or CREATE stored procedure

how to customize SMO script for ALTER or CREATE stored procedure

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones