consolidated script generator for multiple stored procedures
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 ***/
/****** Object: StoredProcedure [dbo].[uspScriptGenerator] ******/IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspScriptGenerator]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[uspScriptGenerator]
GO
/****** Object: StoredProcedure [dbo].[uspScriptGenerator] ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[uspScriptGenerator]
(
@Psp_name_list varchar(max) -- parameter consisting the procedures name and there type ie ('N'ew / 'O'ld) 'Proc1~N|Proc2~O|' or 'Proc3~N|Proc4~O'
)
As
begin
set nocount on
/* table variable to hold the stored proc list */ declare @tbl_sp_name_list table (row_no int identity(1,1)
, sp_name varchar(200)
, sp_status char(1)
)
/* start computing the stored proc generation */ if @Psp_name_list is not null
begin
declare @sp_name_list_str varchar(8000)
, @pipe_pos int
, @tild_pos int
, @curr_row_str varchar(8000)
, @sp_name varchar(200)
, @sp_status char(1)
, @curr_sp_name varchar(200)
, @curr_sp_status char(1)
, @curr_row int
, @max_row int
, @bl_end_of_str char(1)
, @spid int
declare @exec_script varchar(2000)
declare @start_text varchar(200)
declare @end_text varchar(200)
set @spid = @@spid
set @sp_name_list_str = @Psp_name_list
set @bl_end_of_str = 'N'
set @pipe_pos = charindex('|', @sp_name_list_str)
if @@ERROR <> 0 GoTo IGoWithError
if @pipe_pos = 0
begin
set @pipe_pos = len(@sp_name_list_str)
set @bl_end_of_str = 'Y'
end
/* start looping to retrieve the proc name and it's type */ While @pipe_pos <> 0
begin
if @bl_end_of_str = 'N'
begin
set @curr_row_str = substring(@sp_name_list_str, 1, @pipe_pos - 1 )
set @sp_name_list_str = substring(@sp_name_list_str, @pipe_pos + 1 , len(@sp_name_list_str))
if @@ERROR <> 0 GoTo IGoWithError
end
if @bl_end_of_str = 'Y'
begin
set @curr_row_str = substring(@sp_name_list_str, 1, @pipe_pos )
set @sp_name_list_str = ''
if @@ERROR <> 0 GoTo IGoWithError
end
set @tild_pos = charindex ('~', @curr_row_str)
if @tild_pos = 0 GoTo IGoWithError
set @sp_name = case when @tild_pos = 1 then null else substring(@curr_row_str, 1, @tild_pos -1 ) end
set @curr_row_str = substring(@curr_row_str, @tild_pos +1, len(@curr_row_str))
if @@ERROR <> 0 GoTo IGoWithError
set @tild_pos = charindex ('~', @curr_row_str)
set @sp_status = case when @curr_row_str = '' then null else @curr_row_str end
if @@ERROR <> 0 GoTo IGoWithError
/* Check if @sp_name exists */
if (object_id(@sp_name) is not null)
begin
/* Check if @sp_name already considered*/ if not exists(select sp_name from @tbl_sp_name_list where sp_name = @sp_name)
begin
insert into @tbl_sp_name_list (sp_name, sp_status)
values (@sp_name, @sp_status)
end
end
if @@ERROR <> 0 GoTo IGoWithError
set @pipe_pos = charindex('|', @sp_name_list_str)
if @pipe_pos = 0
begin
set @pipe_pos = len(@sp_name_list_str)
set @bl_end_of_str = 'Y'
if @@ERROR <> 0 GoTo IGoWithError
end
end
end
/* Select Number of procedure name passed */ select @max_row = count(*) from @tbl_sp_name_list
if @max_row is not null
begin
declare @tbl_create_sp_script table (sp_name varchar(200) not null
, row_no int null
, sp_text varchar(4000) null)
set @exec_script = 'create table ##tmp_tbl_sp_script_' + cast(@spid as varchar(10)) + ' (row_no int identity(1,1),sp_text varchar(4000) null)'
exec(@exec_script)
if @@ERROR <> 0 GoTo IGoWithError
/* Set the header information for each procedure */ --set @start_text = ' ' + char(10) + char(13)
set @start_text = '/*** Start of script ***/' + char(10)
set @start_text = @start_text + 'GO ' + char(10)
set @start_text = @start_text + 'SET ANSI_NULLS ON ' + char(10)
set @start_text = @start_text + 'GO ' + char(10)
set @start_text = @start_text + 'SET QUOTED_IDENTIFIER ON ' + char(10)
set @start_text = @start_text + 'GO ' + char(10) + char(13)
set @curr_row = 1
while @curr_row <= @max_row
begin
select @curr_sp_name = sp_name from @tbl_sp_name_list where row_no = @curr_row
select @curr_sp_status = sp_status from @tbl_sp_name_list where row_no = @curr_row
insert into @tbl_create_sp_script (sp_name,row_no,sp_text)
select @curr_sp_name,0,@start_text
set @exec_script = null
set @exec_script = 'insert into ##tmp_tbl_sp_script_' + cast(@spid as varchar(10)) + ' (sp_text)'
set @exec_script = @exec_script + ' exec sp_helptext ''' + @curr_sp_name + ''''
exec(@exec_script)
if @@ERROR <> 0 GoTo IGoWithErrorAndDropTable
/* Set the trailer information for each procedure */ set @end_text = char(10) + char(13)
set @end_text = @end_text + 'GO ' + char(10
set @end_text = @end_text + 'SET ANSI_NULLS ON ' + char(10) --+ char(13)
set @end_text = @end_text + 'GO ' + char(10) --+ char(13)
set @end_text = @end_text + 'SET QUOTED_IDENTIFIER OFF ' + char(10)-- + char(13)
set @end_text = @end_text + 'GO ' + char(10) + char(13)
set @end_text = @end_text + '/*** End of script ***/'
set @end_text = @end_text + char(10) + char(13)
/* Reset the variable to insert data in temporary table */ set @exec_script = null
set @exec_script = 'insert into ##tmp_tbl_sp_script_' + cast(@spid as varchar(10)) + ' (sp_text) select ''' + @end_text + ''''
exec(@exec_script)
/* Adjust the procedure script from 'Create' to 'Alter' if it's old one */ set @exec_script = null
set @exec_script = 'update ##tmp_tbl_sp_script_' + cast(@spid as varchar(10))
set @exec_script = @exec_script + ' set sp_text = REPLACE ( sp_text , ltrim(rtrim(substring(sp_text,1,charindex(upper(''procedure''),upper(sp_text)) + 9 ))) , ''Alter Procedure'' )'
set @exec_script = @exec_script + ' where sp_text like ''%create%procedure%'' and upper(''' + @curr_sp_status + ''') = ''O'''
exec(@exec_script)
if @@ERROR <> 0 GoTo IGoWithErrorAndDropTable
/* Retrieve the procedure script and insert into table variable for final script generation */ set @exec_script = null
set @exec_script = 'select ''' + @curr_sp_name + ''',row_no,sp_text'
set @exec_script = @exec_script + ' from ##tmp_tbl_sp_script_' + cast(@spid as varchar(10))
set @exec_script = @exec_script + ' order by row_no'
insert into @tbl_create_sp_script (sp_name,row_no,sp_text)
exec(@exec_script)
if @@ERROR <> 0 GoTo IGoWithErrorAndDropTable
set @exec_script = null
set @exec_script = 'delete ##tmp_tbl_sp_script_' + cast(@spid as varchar(10))
exec(@exec_script)
if @@ERROR <> 0 GoTo IGoWithErrorAndDropTable
set @curr_row = @curr_row + 1
end
select sp_text [All Stored Procedure Script Text]
from @tbl_create_sp_script
order by sp_name,row_no
goto IGoWithoutError
end
IGoWithoutError:
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE name = '##tmp_tbl_sp_script_' + cast(@spid as varchar(10)) )
set @exec_script = 'drop table ##tmp_tbl_sp_script_' + cast(@spid as varchar(10))
exec(@exec_script)
return (1)
IGoWithErrorAndDropTable:
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE name = '##tmp_tbl_sp_script_' + cast(@spid as varchar(10)))
set @exec_script = 'drop table ##tmp_tbl_sp_script_' + cast(@spid as varchar(10))
exec(@exec_script)
raiserror ('Error generating script.',16,1)
return (-1)
IGoWithError:
raiserror ('Error generating script. Please check the supplied stored procedure name(s) and/or value passed in.',16, 1)
return (-1)
end
GO 
