Home Uncategorized “Reflect” a TSQL routine

    “Reflect” a TSQL routine

    471
    0

    Ever want to see the text of a stored procedure, function, or trigger — or manipulate the text in some way?

    sp_helptext works, sort of. But I really don’t like the way it handles large procedures (> 4000 characters). They seem to end up with some strangely wrapped lines and other side-effects that aren’t too nice. So I decided to roll my own custom UDF to do the job. The UDF returns a table containing two columns — a line number (in increments of 100), and a line of text, with a maximum of 4000 characters. Yes, that’s a limitation, but if you have stored procedures with line numbers longer than 4000 characters, you probably have much, much bigger problems than can be solved reading this blog.

    A friend of mine felt that a parallel of some sort could be drawn between this UDF and .NET reflection, so I named it “ReflectRoutine”. I admit, the connection is somewhat tenuous, but it sounds cool, so I’m leaving it. If you can think of a better name, feel free to rename it on your end!

    I’ve commented this one quite heavily — it uses a lot of string manipulation and can get very confusing. At least, it was incredibly confusing to write! So without further ado…

    CREATE FUNCTION dbo.ReflectRoutine
    (
    	@RoutineName VARCHAR(200)
    )
    RETURNS @ReturnTbl TABLE 
    	(
    		LineNum INT IDENTITY(100, 100), 
    		OutParam NVARCHAR(4000)
    	)
    AS
    BEGIN
    	/* n = current colid for the object */
    	DECLARE @n TINYINT
    	SET @n = 1
    
    	/* current is the current line of text we're working on */
    	DECLARE @Current NVARCHAR(4000)
    	SET @Current = ''
    	DECLARE @CurrentLen INT
    	SET @CurrentLen = 0
    
    	/* overflow and overflow2 will contain 
    	characters too big for the current working set */
    	DECLARE @Overflow NVARCHAR(4000)
    	SET @Overflow = ''
    	DECLARE @OverflowLen INT
    	SET @OverflowLen = 0
    	DECLARE @Overflow2 NVARCHAR(4000)
    	SET @Overflow2 = ''
    
    	/* delimiter is our delimiter for string splitting -- crlf */
    	DECLARE @Delimiter NCHAR(2)
    	SET @Delimiter = NCHAR(13)+NCHAR(10)
    
    	/* how many rows of 4000 characters are we dealing with? */
    	DECLARE @maxColId TINYINT
    	SELECT 
    		@maxColId = MAX(colid) + 1
    	from dbo.syscomments
    	WHERE id = OBJECT_ID(@RoutineName)
    
    	WHILE @n <= @maxColId
    	BEGIN
    		/* get the next row of data */
    		IF @n < @maxColId
    		BEGIN
    			SELECT @Current = [text]
    			FROM dbo.syscomments
    			WHERE id = OBJECT_ID(@RoutineName)
    				AND ColId = @n
    		END
    		ELSE
    		BEGIN
    			/* Work only with the overflow */
    			SELECT @Current = ''
    		END
    
    		SET @CurrentLen = DATALENGTH(@Current)/2
    
    		/* The current overflow value will be pushed into the front
    		of the current row -- we need to pull off enough characters from the 
    		end to make room for the current overflow plus the current string
    		and end up with at most 4000 characters.  Overflow2 is the characters
    		that will be pulled from the end */
    
    		SET @Overflow2 = 
    			RIGHT(@Current,
    				CASE
    					WHEN (@CurrentLen + @OverflowLen) > 4000 THEN 
    						(@CurrentLen + @OverflowLen) - 4000
    					ELSE 0
    				END)
    
    		/* Now we push the previous overflow into the front of the value and
    		get rid of the characters from the end that are now in overflow2 --
    		leaving us with a current value that's at most 4000 characters (which 
    		is the limit for nvarchar) */
    
    		SET @Current = @Overflow + LEFT(@Current, @CurrentLen - DATALENGTH(@Overflow2)/2)
    		SET @CurrentLen = DATALENGTH(@Current)/2
    
    		/* Re-calculate the overflow value.  Find the last occurrence of the 
    		delimiter (crlf) within the first 3996 characters of the string.  The
    		rest of the string after that delimiter will be split on the next
    		round (assume that there could be some concatenation needed with the
    		next chunk, so we can't	split until that's done) 
    
    		Also, surround the current value with two delimiters so that the split
    		algorithm will work properly */
    
    		SELECT @Overflow = RIGHT(@Current, 
    			CASE 
    				WHEN @CurrentLen - MAX(Number) - 1 < 0 THEN 0
    				ELSE @CurrentLen - MAX(Number) - 1
    			END) + @Overflow2,
    			@Current = @Delimiter + LEFT(@Current, MAX(Number) - 1) + @Delimiter
    		FROM dbo.Numbers
    		WHERE (SUBSTRING(@Current, Number, 2) = @Delimiter
    				OR Number = @CurrentLen + 1)
    			AND Number <= 3996
    
    		SET @CurrentLen = DATALENGTH(@Current)/2
    		SET @OverflowLen = DATALENGTH(@Overflow)/2
    
    		/* finally, insert the lines for this round... */
    		INSERT	@ReturnTbl (OutParam)
    		SELECT	SUBSTRING(@Current, Number + 2,
    				CHARINDEX(@Delimiter, @Current, Number + 2) - Number - 2)
    		FROM	dbo.Numbers
    		WHERE 	Number <= @CurrentLen - 2
    			AND SUBSTRING(@Current, Number, 2) = @Delimiter
    			AND @CurrentLen > 4
    		ORDER BY Number
    
    		SET @n = @n + 1
    	END
    
    	RETURN
    END
    

    Note, it requires a numbers table — but regular readers will probably ask, “what doesn’t?”

    Using it is quite easy… For instance, once it’s created if you wish to reflect the UDF itself:

    SELECT *
    FROM dbo.ReflectRoutine('ReflectRoutine')
    

    I recommend Query Analyzer’s “Results in Text” mode with “maximum characters per column” set to a number greater than 4000 — of course, none of my stored procedures have lines greater than about 150 characters long, so that’s probably not a huge deal for most people. As I said, if your lines are that long, you have bigger problems!


    Update, December 1, 2005: Don’t use LEN when you mean DATALENGTH! I used LEN in the original version of this function and it caused some strange truncations to occur when an overflow happened right at some white space (LEN does not count white space to the right). That bug is fixed in the version now posted. 

    Previous articleScript out PKs/UNIQUE constraints and referencing FKs
    Next articleTokenize UDF
    Adam Machanic helps companies get the most out of their SQL Server databases. He creates solid architectural foundations for high performance databases and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has contributed to numerous books on SQL Server development. A long-time Microsoft MVP for SQL Server, he speaks and trains at IT conferences across North America and Europe.