A modified version of sp_helptext in SQL Server

Deviprasads
Posted by in Sql Server category on for Advance level | Points: 250 | Views : 14763 red flag
Rating: 5 out of 5  
 2 vote(s)

One of the most favorite Microsoft shipped Stored Procedure is sp_helptext and it can be used very often in our daily development job. Here we are going to modify that sp's logic little bit to help us more efficiently.


 Download source code for A modified version of sp_helptext in SQL Server

Introduction:

I am a big fan of Microsoft and love to explore their technologies. As a part of my job, I do review SQL codes written by different developers and pass them to the next level. I use sp_helptext very often to get texts of SQL Server objects. One fine day, I thought this could have been better and more useful.. 

Here I go.. Just be with me till the end of this article.. Trust me it's gonna be fun..

Objective:

As you know, sp_helptext gives us text or script for a sql server object (SP/Fun/Views). It takes system object names as it's parameter.
eg: EXEC sp_helptext uspGetBillOfMaterials


Now there are two things needs to be observed here..
  1. This Microsoft shipped SP gives us only text/script for a object with only 'CREATE TO' logic.
  2. This can be executed only for a single object at a single point of time.

Now I have two questions for all of you..
  1. What if we need something which would give us text/script for a object with 'DROP AND CREATE TO' logic?
  2. How can we get multiple object's scripts in a single batch?
Note: The primary solution for question No 1 is, we can get this in SQL Server object explorer menu, under the object path. You will have to go all the way down to get a simple 'DROP and CREATE TO' script for a object. I personally feel this is little tough, when you have a thousand of objects in your instance. 

Here we are going to solve our two requirements..

Lab exercise:

Lets first look at the sp_helptext (MS Shipped object).. I assume you already know about sp_helptext so I am directly sharing screen captures without explaining more about it.. For more reference click here

Screen capture: sp_helptexts

Requirement (1): Get object(SP, Fun & View) texts with 'DROP AND CREATE TO' logic.

To achieve this, I have played around sp_helptext's code and added little logic inside. This's is not a rocket science and you will find it very simple. Here I am directly pasting the codes from my SSMS.

As you all know we can't alter any MS Shipped sps, so we will have to create a new SP separately inside our environment. I have named it usp_helptext because it functions very similar to sp_helptext.
/*************************************************************   
** File:     [usp_helptext]  
** Author:   Prasad Sahoo
** Description: Modified version of sp_helptext(MS Shipped SP).
** Purpose:   script object as drop and create to functionality
** Date:   05/24/2013
  
**************************************************************   
** Change History   
**************************************************************   
** PR   Date        Author		Change Description    
** --   --------    -------		-----------------------------  
** 1    05/31/2013  Prasad Sahoo	Made [] independent for objects
** 2    06/06/2013  Prasad Sahoo	Schema prefetch for objects 
**************************************************************/    
ALTER PROCEDURE usp_helptext
@objname nvarchar(776)
,@columnname sysname = NULL
AS

SET NOCOUNT ON
DECLARE @dbname sysname
,@objid	INT
,@BlankSpaceAdded   INT
,@BasePos       INT
,@CurrentPos    INT
,@TextLength    INT
,@LineId        INT
,@AddOnLen      INT
,@LFCR          INT --lengths of line feed carriage return
,@DefinedLength INT
,@ObName nvarchar(700) 
,@ObType nvarchar(50)
,@ObSchemaName nvarchar(20)
,@ObNameForSchema nvarchar(700)
,@NewObjName nvarchar(1000)
,@SyscomText	nvarchar(4000)
,@Line          nvarchar(255)

IF LEFT(@objname, 1) != '['
BEGIN
SET @obname = '['+@objname+']'
SET @ObNameForSchema = @objname
END

ELSE 
BEGIN
SET @obname = @objname
SET @ObNameForSchema = SUBSTRING(@objname, 2, LEN(@objname) - 2)
END

SELECT @ObType = type_desc FROM sys.objects WHERE object_id = object_id(@obname)
SELECT @ObSchemaName =  '[' + OBJECT_SCHEMA_NAME((SELECT object_id FROM sys.objects WHERE name = @ObNameForSchema)) + ']'

SELECT @DefinedLength = 255
SELECT @BlankSpaceAdded = 0 
/*Keeps track of blank spaces at end of lines. Note Len function ignores
          trailing blank spaces*/
          
CREATE TABLE #CommentText
(LineId	INT
,Text  nvarchar(255))

/*
**  Make sure the @objname is local to the current database.
*/

SELECT @dbname = PARSENAME(@objname,3)
IF @dbname IS NULL
	SELECT @dbname = DB_NAME()

ELSE IF @dbname <> DB_NAME()
        BEGIN
                RAISERROR(15250,-1,-1)
                RETURN (1)
        END
/*
**  See if @objname exists.
*/

SELECT @objid = OBJECT_ID(@objname)
IF (@objid is null)
        BEGIN
		RAISERROR(15009,-1,-1,@objname,@dbname)
		RETURN (1)
        END

-- If second parameter was given.
IF ( @columnname is not null)

    BEGIN
        -- Check if it is a table
        IF (SELECT count(*) FROM sys.objects WHERE object_id = @objid and TYPE in ('S ','U ','TF'))=0
            BEGIN
                RAISERROR(15218,-1,-1,@objname)
                RETURN(1)
            END

        -- check if it is a correct column name

        IF ((SELECT 'count'=count(*) FROM sys.columns WHERE name = @columnname and object_id = @objid) =0)
            BEGIN
                RAISERROR(15645,-1,-1,@columnname)
                RETURN(1)
            END

		IF (ColumnProperty(@objid, @columnname, 'IsComputed') = 0)

		BEGIN
			RAISERROR(15646,-1,-1,@columnname)
			RETURN(1)
		END

        DECLARE ms_crs_syscom  CURSOR LOCAL

        FOR SELECT text FROM syscomments WHERE id = @objid AND encrypted = 0 AND number =
                        (SELECT column_id FROM sys.columns WHERE name = @columnname and object_id = @objid)
                        ORDER BY number,colid
        FOR READ ONLY
    END

ELSE IF @objid < 0	-- Handle system-objects

	BEGIN
		-- Check count of rows with text data
		IF (SELECT count(*) from master.sys.syscomments WHERE id = @objid and text is not null) = 0
			BEGIN
				RAISERROR(15197,-1,-1,@objname)
				RETURN (1)
			END

DECLARE ms_crs_syscom CURSOR LOCAL FOR SELECT text FROM master.sys.syscomments WHERE id = @objid
	ORDER BY number, colid FOR READ ONLY
	END

ELSE
    BEGIN
		/*
        **  Find out how many lines of text are coming back,
        **  and return if there are none.
        */
        IF (SELECT count(*) FROM syscomments c, sysobjects o WHERE o.xtype not in ('S', 'U')
		            and o.id = c.id and o.id = @objid) = 0
                BEGIN
                        RAISERROR(15197,-1,-1,@objname)
                        RETURN (1)
                END
        IF (SELECT count(*) FROM syscomments WHERE id = @objid and encrypted = 0) = 0
                BEGIN
                        RAISERROR(15471,-1,-1,@objname)
                        RETURN (0)
                END

		DECLARE ms_crs_syscom  CURSOR LOCAL
		FOR SELECT text FROM syscomments WHERE id = @objid and encrypted = 0
				ORDER BY number, colid
		FOR READ ONLY
    END

/*
**  else get the text.
*/

SELECT @LFCR = 2
SELECT @LineId = 1


OPEN ms_crs_syscom

FETCH NEXT from ms_crs_syscom into @SyscomText
WHILE @@fetch_status >= 0

BEGIN
    SELECT  @BasePos    = 1
	SELECT  @CurrentPos = 1
    SELECT  @TextLength = LEN(@SyscomText)

    WHILE @CurrentPos  != 0
    BEGIN
        --Looking for end of line followed by carriage return
        SELECT @CurrentPos =   CHARINDEX(char(13)+char(10), @SyscomText, @BasePos)
        --If carriage return found
        IF @CurrentPos != 0
        BEGIN
            /*If new value for @Lines length will be > then the
            **set length then insert current contents of @line
            **and proceed.
            */
            WHILE (isnull(LEN(@Line),0) + @BlankSpaceAdded + @CurrentPos-@BasePos + @LFCR) > @DefinedLength
            BEGIN
                SELECT @AddOnLen = @DefinedLength-(isnull(LEN(@Line),0) + @BlankSpaceAdded)
                INSERT #CommentText VALUES
                ( @LineId, isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N''))
                SELECT @Line = NULL, @LineId = @LineId + 1, @BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
            END

            SELECT @Line    = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos-@BasePos + @LFCR), N'')
            SELECT @BasePos = @CurrentPos+2

            INSERT #CommentText VALUES( @LineId, @Line )
            SELECT @LineId = @LineId + 1
            SELECT @Line = NULL
        END

       ELSE
        --else carriage return not found
        BEGIN
            IF @BasePos <= @TextLength
            BEGIN
                /*If new value for @Lines length will be > then the
                **defined length
                */
                WHILE (ISNULL(LEN(@Line),0) + @BlankSpaceAdded + @TextLength-@BasePos+1 ) > @DefinedLength
                begin
                    SELECT @AddOnLen = @DefinedLength - (isnull(LEN(@Line),0) + @BlankSpaceAdded)
                    INSERT #CommentText VALUES
                    ( @LineId, isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N''))

                    SELECT @Line = NULL, @LineId = @LineId + 1, @BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
                END

                SELECT @Line = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength-@BasePos+1 ), N'')

                IF LEN(@Line) < @DefinedLength and charindex(' ', @SyscomText, @TextLength+1 ) > 0
                BEGIN
                    SELECT @Line = @Line + ' ', @BlankSpaceAdded = 1
                END

            END

        END

    END

	FETCH NEXT from ms_crs_syscom into @SyscomText

END

IF @Line is NOT NULL
	BEGIN
	INSERT #CommentText VALUES( @LineId, @Line )
	END
	
CLOSE  ms_crs_syscom
DEALLOCATE 	ms_crs_syscom

IF (SELECT type FROM sys.objects WHERE object_id = OBJECT_ID(@objname)) IN (N'P', N'PC')
	BEGIN
		PRINT @obname + ' = ' + @ObType
	INSERT INTO #CommentText (LineId, Text) VALUES
	(NULL, 'USE [' + @dbname +']')
	,(NULL, 'GO')
	,(NULL, '')
	,(NULL, '/****** Object:  StoredProcedure ' + @ObSchemaName + '.' + @obname +'   Script Date: '+ CONVERT(varchar(50), getdate(), 101) + ' ' + CONVERT(VARCHAR(12), GETDATE(), 114) +' ******/')
	,(NULL, 'IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'''+ @ObSchemaName +'.'+ @obname +''') AND type in (N''P'', N''PC''))')
	,(NULL, 'DROP PROCEDURE ' + @ObSchemaName + '.' + @obname)
	,(NULL, 'GO')
	,(NULL, '')
	,(NULL, 'USE [' + @dbname +']')
	,(NULL, 'GO')
	,(NULL, '')
	,(NULL, '/****** Object:  StoredProcedure ' + @ObSchemaName + '.' + @obname +'   Script Date: '+ CONVERT(varchar(50), getdate(), 101) + ' ' + CONVERT(VARCHAR(12), GETDATE(), 114) +' ******/')
	,(NULL, 'SET ANSI_NULLS ON')
	,(NULL, 'GO')
	,(NULL, '')
	,(NULL, 'SET QUOTED_IDENTIFIER ON')
	,(NULL, 'GO')
	,(NULL, '')
END

ELSE IF (SELECT type FROM sys.objects WHERE object_id = OBJECT_ID(@obname)) IN (N'FN', N'IF', N'TF', N'FS', N'FT')
	BEGIN
		PRINT @obname + ' = ' + @ObType
		INSERT INTO #CommentText VALUES 
	(NULL,  'USE [' + @dbname +']')
	,(NULL, 'GO') 
	,(NULL, '')
	,(NULL, '/****** Object:  UserDefinedFunction ' + @ObSchemaName + '.' + @obname +'   Script Date: '+ CONVERT(varchar(50), getdate(), 101) + ' ' + CONVERT(VARCHAR(12), GETDATE(), 114) +' ******/')
	,(NULL, 'IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'''+ @ObSchemaName +'.'+ @obname +''') AND type in (N''FN'', N''IF'', N''TF'', N''FS'', N''FT''))')
	,(NULL, 'DROP FUNCTION '+ @ObSchemaName + '.' + @obname)
	,(NULL, 'GO')
	,(NULL, '')
	,(NULL, 'USE [' + @dbname +']')
	,(NULL, 'GO') 
	,(NULL, '')
	,(NULL, '/****** Object:  UserDefinedFunction ' + @ObSchemaName + '.' + @obname +'   Script Date: '+ CONVERT(varchar(50), getdate(), 101) + ' ' + CONVERT(VARCHAR(12), GETDATE(), 114) +' ******/')
	,(NULL, 'SET ANSI_NULLS ON')
	,(NULL, 'GO')
	,(NULL, '')
	,(NULL, 'SET QUOTED_IDENTIFIER ON')
	,(NULL, 'GO')
	,(NULL, '')

END

ELSE IF (SELECT type FROM sys.objects WHERE object_id = OBJECT_ID(@obname)) IN (N'V')
	BEGIN
		PRINT @obname + ' = ' + @ObType
		INSERT INTO #CommentText VALUES 
	(NULL,  'USE [' + @dbname +']')
	,(NULL, 'GO') 
	,(NULL, '')
	,(NULL, 'IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'''+ @ObSchemaName +'.'+ @obname +'''))')
	,(NULL, '/****** Object:  UserDefinedFunction ' + @ObSchemaName + '.' + @obname +'   Script Date: '+ CONVERT(varchar(50), getdate(), 101) + ' ' + CONVERT(VARCHAR(12), GETDATE(), 114) +' ******/')
	,(NULL, 'DROP '+ @ObType+ ' ' + @ObSchemaName + '.' + @obname)
	,(NULL, 'GO')
	,(NULL, '')
	,(NULL, 'SET ANSI_NULLS ON')
	,(NULL, 'GO')
	,(NULL, '')
	,(NULL, 'SET QUOTED_IDENTIFIER ON')
	,(NULL, 'GO')
	,(NULL, '')
END

ELSE
BEGIN
PRINT 'This object has either no text or incorrect name. ' + @ObType + ' ' + @obname
END

SELECT Text from #CommentText order by LineId
DROP TABLE #CommentText
return (0) -- usp_helptext
Screen capture: usp_helptext


We are done with our first requirement.. Lets move to the next..

Requirement (2): Get multiple object's DROP AND CREATE to in a single batch.

To achieve this we need two things.
  • One function, which would give us CSV values into a table format. [udf_CSVSplit]
  • One more stored procedure, which would execute sp_helptext in a cursor for each object in that function table. [usp_helptexts]
Now lets create above objects one by one..

udf_CSVSplit:
 /*  
[Author]:Prasad Sahoo 
[Date of Creation]: 05/25/2013  
[Purpose]: Split the String by comma separator and insert into a table.  
*/  
CREATE FUNCTION [dbo].[udf_CSVSplit]    
(    
 @List varchar(max), @Delimiter varchar(10)    
)    
RETURNS @Ids TABLE    
(Id VARCHAR(MAX)) AS    
BEGIN    
   DECLARE @x xml  
  SELECT @x = CONVERT(xml,'<root><s>' + REPLACE(@List,@Delimiter,'</s><s>') + '</s></root>')  
    
  INSERT INTO @Ids(ID)   
  SELECT [ID] = T.c.value('.','varchar(50)')   
  FROM @X.nodes('/root/s') T(c)  
    
  RETURN  
 END     
Note: We don't have to call this function anywhere explicitly, this is being called implicitly from usp_helptexts.


usp_helptexts:
/*************************************************************   
** File:     [usp_helptexts]  
** Author:   Prasad Sahoo
** Description: Child script of usp_helptext
** Purpose:   Merged script for multiple input objects
** Date:   06/07/2013
  
**************************************************************   
** Change History   
**************************************************************   
** PR   Date        Author		Change Description    
** --   --------    -------	-----------------------------  
** 1    06/07/2013  Prasad Sahoo		Created
**************************************************************/    

CREATE PROC usp_helptexts
(
@ObjNames nvarchar(max)
)
as
begin
SET NOCOUNT ON 

DECLARE @dbname varchar(50)
set @dbname = 'USE [' + (SELECT DB_NAME()) + ']'

CREATE TABLE #MergedScript
(Texts varchar(max))

DECLARE @CurObj varchar(max)
DECLARE @CurSelect CURSOR
SET @CurSelect = CURSOR FOR
SELECT RTRIM(LTRIM(ID)) FROM (SELECT ID FROM udf_CSVSplit(@ObjNames, ',')) Temp

OPEN @CurSelect
FETCH NEXT
FROM @CurSelect INTO @CurObj
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #MergedScript (Texts)
EXECUTE Usp_helptext @CurObj
INSERT INTO #MergedScript (Texts)
VALUES (''), ('')
FETCH NEXT
FROM @CurSelect INTO @CurObj
END
CLOSE @CurSelect
DEALLOCATE @CurSelect

UPDATE #MergedScript SET Texts = '' where Texts = @dbname
SELECT Texts FROM #MergedScript
DROP TABLE #MergedScript
end
I have named this sp as usp_helptexts because it's taking multiple objects as parameter.

We are done now! Inside above stored procedure, a cursor has been declared and being used to execute usp_helptext stored proc for multiple object names from udf_CSVSplit function table.

Screen capture: usp_helptexts


Conclusion:

In this way we can generate any object(SP, Fun & Views) scripts and execute them easily on any of our environments. There are many tools in the market which will give you object scripts in a single batch for hassle free deployment but this is something cool and we don't need any 3rd party help.

I did this to help my self and shared here to help community. Remember, Sharing is caring.. 

Caution: Do not test this on your production environment. I recommend to use in your dev environment only.
Page copy protected against web site content infringement by Copyscape

About the Author

Deviprasads
Full Name: Deviprasad sahoo
Member Level: Starter
Member Status: Member
Member Since: 6/8/2012 3:40:15 AM
Country: India
S Devi Prasad
http://www.sqlindia.com
currently working as ASE in an USA based health care company.

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)