Generate Stored Procedure/Functions Script with SP

/* 

Objective    :Procedure used to generate the DB script of Stored Procedures  and User defined funtions
Parameters   :
@sp_list : list of Stored procedures or funtions separated by comma(,)
Output  : DB script will be displayed on the screen for all the SPs/Functions in the @sp_list

This procedure will take care of the tab, space, carriage return characters in the list.
User can run this SP as follows :

Exec sp_Generate_Script 'CustOrderHist,
CustOrdersDetail,
CustOrdersOrders,
First_Day'

        Note : Choose text format for result display

*/
CREATE  PROCEDURE sp_Generate_Script
    @sp_list    Varchar(8000)
AS
Begin
SET NOCOUNT ON

Declare @strSPText  Varchar(8000);
Declare @sp_name    Varchar(500);
Declare @sp_list_cp Varchar(8000);
Declare @objType    Varchar(10) ;
Declare @Delimiter    Varchar(1);
Declare @endflag       Bit ;
Declare @recordStatus  Bit ;
Declare @ProcTextTable TABLE (ProcText Varchar(8000))

Set @Delimiter  = ',';
Set @sp_list_cp = @sp_list;
Set @endflag    = 0;

/*  Loop thru and get each SP name from the list and generate the drop scripts...  */While CHARINDEX(@Delimiter,@sp_list,0) <> 0 OR len(@sp_list)>0
Begin
If (CHARINDEX(@Delimiter,@sp_list,0) <> 0)  -- List has more items
Begin
Set @sp_name=RTRIM(LTRIM(SUBSTRING(@sp_list,1,CHARINDEX(@Delimiter,@sp_list,0)-1)))
Set @sp_list=RTRIM(LTRIM(SUBSTRING(@sp_list,CHARINDEX(@Delimiter,@sp_list,0)+1,LEN(@sp_list))))
End

Else -- No item in the list
Begin
Set @sp_name = @sp_list;
Set @endflag = 1;
End

Set @sp_name = RTRIM(LTRIM(Replace(Replace(Replace(@sp_name,CHAR(9),''),CHAR(13),''),CHAR(10),'')));

If (len(@sp_name)>0)
Begin
Select @objType = xtype From dbo.sysobjects Where SysObjects.Name = @sp_name 

If (@objType='P')
Begin
Insert Into @ProcTextTable Values('if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].['+@sp_name +']'') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)')
Insert Into @ProcTextTable Values('drop procedure [dbo].['+ @sp_name +']')
Insert Into @ProcTextTable Values('GO')
Insert Into @ProcTextTable Values('')
End

Else 
If (@objType = N'FN' or @objType = N'IF' or @objType = N'TF')
Begin
Insert Into @ProcTextTable Values( 'if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].['+@sp_name +']'') and xtype in (N''FN'', N''IF'', N''TF''))')
Insert Into @ProcTextTable Values( 'drop function [dbo].[' +@sp_name +']')
Insert Into @ProcTextTable Values( 'GO')
End
End
If (@endflag=1) BREAK;
End


Set @endflag   = 0;
Set @sp_list   = @sp_list_cp;

/*  Loop thru and get each SP name from the list and generate the Create scripts... */While CHARINDEX(@Delimiter,@sp_list,0) <> 0 OR len(@sp_list)>0
Begin
If (CHARINDEX(@Delimiter,@sp_list,0) <> 0)  -- List has more items
Begin
Set @sp_name=RTRIM(LTRIM(SUBSTRING(@sp_list,1,CHARINDEX(@Delimiter,@sp_list,0)-1)))
Set @sp_list=RTRIM(LTRIM(SUBSTRING(@sp_list,CHARINDEX(@Delimiter,@sp_list,0)+1,LEN(@sp_list))))
End

Else -- No item in the list
Begin
Set @sp_name = @sp_list;
Set @endflag = 1;
End

Set @sp_name = RTRIM(LTRIM(Replace(Replace(Replace(@sp_name,CHAR(9),''),CHAR(13),''),CHAR(10),'')));

If (len(@sp_name)>0 )
Begin
Declare SpText_Cursor Cursor For
Select SysComments.Text
From SysObjects,SysComments   
Where (SysObjects.type='P' or SysObjects.type= N'FN' or SysObjects.type = N'IF' or 
   SysObjects.type = N'TF')
       and (SysObjects.Category = 0)
       and (SysObjects.ID = SysComments.ID) 
   and upper(SysObjects.Name) = upper(ltrim(rtrim(@sp_name)))
Order By SysObjects.Name, SysComments.colid ASC

Open SpText_Cursor

Fetch Next From SpText_Cursor Into @strSPText 

Set  @recordStatus = 0 ;
If (@@FETCH_STATUS = 0)
Begin
Insert Into @ProcTextTable Values( 'SET QUOTED_IDENTIFIER ON ')
Insert Into @ProcTextTable Values( 'GO')
Insert Into @ProcTextTable Values( 'SET ANSI_NULLS ON ')
Insert Into @ProcTextTable Values( 'GO')
Insert Into @ProcTextTable Values( '')
Set  @recordStatus = 1 ;
End

While @@FETCH_STATUS = 0
Begin
Insert Into @ProcTextTable Values( @strSPText); 
    Fetch Next From SpText_Cursor Into @strSPText 
End

Close SpText_Cursor
Deallocate SpText_Cursor

If (@recordStatus = 1)
Begin
Insert Into @ProcTextTable Values( '')
Insert Into @ProcTextTable Values( 'GO')
Insert Into @ProcTextTable Values( 'SET QUOTED_IDENTIFIER OFF ')
Insert Into @ProcTextTable Values( 'GO')
Insert Into @ProcTextTable Values( 'SET ANSI_NULLS ON ')
Insert Into @ProcTextTable Values( 'GO')
Insert Into @ProcTextTable Values( '')
End
End
If (@endflag=1) BREAK;
End

Select * from @ProcTextTable
End