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.