Generate Stored Procedure/Functions Script with SP

,

It will help to generate DBscript of Stored Procedure/User defined Functions in SQL server without selecting those thru Enterprise manager.

Pass the name of Stored procedures/Functions as parameters to the sp_Generate_Script, it generates the scripts and displayed on screen. Choose text format for result display.

/* 

	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

Rate

3 (5)

Share

Share

Rate

3 (5)