“Reflect” a TSQL routine

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.