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

Rate

3 (2)

Share

Share

Rate

3 (2)