THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

"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)
LineNum INT IDENTITY(100, 100),
OutParam NVARCHAR(4000)
/* n = current colid for the object */
SET @n = 1

/* current is the current line of text we're working on */
SET @Current = ''
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? */
@maxColId = MAX(colid) + 1
from dbo.syscomments
WHERE id = OBJECT_ID(@RoutineName)

WHILE @n <= @maxColId
/* get the next row of data */
IF @n < @maxColId
SELECT @Current = [text]
FROM dbo.syscomments
WHERE id = OBJECT_ID(@RoutineName)
AND ColId = @n
/* Work only with the overflow */
SELECT @Current = ''

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 =
WHEN (@CurrentLen + @OverflowLen) > 4000 THEN
(@CurrentLen + @OverflowLen) - 4000

/* 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,
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

SET @n = @n + 1


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:


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!

So does this thing have any application? Watch this space. More on that coming soon...

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. By the way, in case you're reading this for the first time, the application for this is TSQLMacro.
Published Wednesday, July 12, 2006 10:33 PM by Adam Machanic
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS


No Comments

Leave a Comment


About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog


Privacy Statement