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…
[sql]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
[/sql]
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:
[sql]SELECT *
FROM dbo.ReflectRoutine(‘ReflectRoutine’)
[/sql]
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.
