Aaron Bertrand (blog | twitter) posted recently about the benefits of the OBJECT_DEFINITION() function when compared to INFORMATION_SCHEMA.ROUTINES when trying to search for a particular string in a very long procedure. He highights how useful the OBJECT_DEFINITION() function and sys.modules DMV are when searching for words or object references in such long SQL modules. OBJECT_DEFINITION() also makes it very easy to view the properly formatted content of a stored procedure or other module thus replacing that old faithful sp_helptext, which is fine until you hit the 8000-ish character limit in SSMS.
In this post, I describe a way of viewing very long strings or procedures in a way that respects all line feeds and carriage returns without the need to switch results between “grid” and “text” modes.
I must admit, I used to use OBJECT_DEFINITION() all the time then, when I moved to a different client I lost a bunch of frequently used troubleshooting scripts – including that one. And then for the life of me could never remember the function name when I needed it and sp_helptext was there, like an old friend, for a sneaky peak into a procedure or other module. However, although sp_helptext might break up the code in unexpected places, it will at least let you look at the entirety of a stored procedure longer than 8000 characters (as long as you don’t mind switching results to “text” mode. By the way if you weren’t already aware of them, (CTRL + T) is the keyboard shortcut for results to “text” and CTRL + D returns results to “grid” mode.
This is where OBJECT_DEFINITION() or sys.modules fall down slightly – they’re great for searching but, due to a limitation in SSMS that restricts results to 8000 characters, they’re not so good for displaying the code. Now I know that right-clicking the module and choosing “Script as… | CREATE to… | New Query Editor Window” is dead easy, once you’ve navigated the four or so levels to get to the module of interest but sometimes I just want to get at the code quickly from the keyboard without all the mouse clicks.
My open source logging library Log4TSql proposes logging progress info in large procedures as a series of strings appended to a single varchar(max) variable separated by line feeds which is then stored on completion. This minimises IO impact from logging and ensures that progress can be persisted outside of any rolled back transaction. So if a developer is going to store twenty or thirty or three hundred lines of log info in a single variable, there needs to be a mechanism to display that information in a user-friendly way. So Log4TSql has a procedure called [PrintString] which does just that. What’s more it actually prints the results rather than returns them as a result set to save having to switch result between “grid” and “text”.
You can download the full version of [PrintString] together with the code blocks below here but in a nutshell, the code looks like this. It accepts an nvarchar(max) string plus an optional maximum print length which governs how very long strings without any type of line feed will be broken up. The maximum number of unicode characters supported by SQL Server’s PRINT command is 4000 so you cannot supply a value higher than this.
Within the procedure, we look for any line feeds and if found print out the results line by line separated by those line breaks. If the original input string or any single string is longer than @MaxPrintLength, then we print out the text in the specified sized chunks.
CREATE PROCEDURE [PrintString] ( @InputString nvarchar(max) = NULL , @MaxPrintLength int = 4000 ) AS BEGIN SET NOCOUNT ON --! CONSTANTS (keep it SQL2005 compatible) DECLARE @LF char ( 1 ); SET @LF = CHAR(10); DECLARE @CR char ( 1 ); SET @CR = CHAR(13); DECLARE @CRLF char ( 2 ); SET @CRLF = CHAR(13) + CHAR(10); DECLARE @LINE_BREAK char ( 3 ); SET @LINE_BREAK = '%' + @LF + '%'; --! Working Values DECLARE @WorkingLength bigint DECLARE @WorkingString nvarchar ( max ) DECLARE @SubString nvarchar ( max ) DECLARE @SubStringLength bigint --! Validate/correct inputs SET @MaxPrintLength = COALESCE(NULLIF(@MaxPrintLength, 0), 4000) IF @MaxPrintLength > 4000 BEGIN RAISERROR('The @MaxPrintLength value of %d is greater than the maximum length supported by PRINT for nvarchar strings (4000)', 17, 1, @MaxPrintLength); RETURN(60000); END --! Working variables DECLARE @InputLength bigint = LEN(@InputString) IF @InputLength = 0 GOTO OnComplete; --! --! Our input string may contain either carriage returns, line feeds or both --! to separate printing lines so we need to standardise on one of these (LF) --! SET @WorkingString = REPLACE(REPLACE(@InputString, @CRLF, @LF), @CR, @LF); --! --! If there are line feeds we use those to break down the text --! into individual printed lines, otherwise we print it in --! bite-size chunks suitable for consumption by PRINT --! IF PATINDEX(@LINE_BREAK, @InputString) > 0 BEGIN --[BREAK_BY_LINE_FEED] --! Add a line feed on the end so the final iteration works as expected SET @WorkingString = @WorkingString + @LF; SET @WorkingLength = LEN(@WorkingString); DECLARE @LineFeedPos bigint = 0 WHILE @WorkingLength > 0 BEGIN --! --! Get the position of the next line feed --! SET @LineFeedPos = PATINDEX(@LINE_BREAK, @WorkingString); IF @LineFeedPos > 0 BEGIN SET @SubString = SUBSTRING(@WorkingString, 1, @LineFeedPos - 1); SET @SubStringLength = LEN(@SubString); --! --! If this string is too long for a single PRINT, we pass it back --! to PrintString which will process the string in suitably sized chunks --! IF LEN(@SubString) > @MaxPrintLength EXEC [PrintString] @InputString = @SubString ELSE PRINT @SubString; --! Remove the text we've just processed SET @WorkingLength = @WorkingLength - @LineFeedPos; SET @WorkingString = SUBSTRING(@WorkingString, @LineFeedPos + 1, @WorkingLength); END END END --[BREAK_BY_LINE_FEED] ELSE BEGIN --[BREAK_BY_LENGTH] --! --! If there are no line feeds we may have to break it down --! into smaller bit size chunks suitable for PRINT --! IF @InputLength > @MaxPrintLength BEGIN SET @WorkingString = @InputString; SET @WorkingLength = LEN(@WorkingString); SET @SubStringLength = @MaxPrintLength; WHILE @WorkingLength > 0 BEGIN SET @SubString = SUBSTRING(@WorkingString, 1, @SubStringLength); SET @SubStringLength = LEN(@SubString) --! --! If we still have text to process, set working values --! IF (@WorkingLength - @SubStringLength + 1) > 0 BEGIN PRINT @SubString; --! Remove the text we've just processed SET @WorkingString = SUBSTRING(@WorkingString, @SubStringLength + 1, @WorkingLength); SET @WorkingLength = LEN(@WorkingString); END END END ELSE PRINT @InputString; END --[BREAK_BY_LENGTH] --///////////////////////////////////////////////////////////////////////////////////////////////// OnComplete: --///////////////////////////////////////////////////////////////////////////////////////////////// SET NOCOUNT OFF RETURN END
So first of all, we need a stored procedure that is long enough to prove this works. This next block of code will drop and create a procedure called [VeryLongProcedure] which has 9114 characters – too long to display in SSMS using OBJECT_DEFINITION().
IF OBJECTPROPERTY(OBJECT_ID(N'[VeryLongProcedure]'), N'IsProcedure') = 1 DROP PROCEDURE [VeryLongProcedure]; GO DECLARE @sql varchar(max), @LineNum int = 0, @NumLines int = 80 SET @sql = 'CREATE PROCEDURE [VeryLongProcedure]' + CHAR(10) + 'AS' + CHAR(10) + 'BEGIN' WHILE @LineNum < @NumLines BEGIN SET @LineNum += 1; SET @sql = @sql + CHAR(10) + ' PRINT ''Line Number: ' + REPLICATE('0', 4 - LEN(CAST(@LineNum AS varchar))) + CAST(@LineNum AS varchar) + ' - ' + REPLICATE('X', 79) + ''';' END SET @sql = @sql + CHAR(10) + 'PRINT ''End Of The Line'';' + CHAR(10) + 'END' SELECT LEN(@sql) AS [TextLength] EXEC (@sql);
Next let’s see what sp_helptext makes of this…
Ah, that is about as much use as a chocolate fireguard!
Lets try something a bit more up to date…
As you can see OBJECT_DEFINITION() displays the text as it was written (I’ve removed the intemediate lines for brevity) but we’re still not getting the whole story, the content of our procedure is still being truncated. Please understand, this is not a limitation on OBJECT_DEFINITION(), it is SQL Server Management Studio (SSMS) that will not display more than about 8000 characters.
Ok, lets see what [PrintString] makes of this…
DECLARE @string nvarchar(max) = OBJECT_DEFINITION(OBJECT_ID('VeryLongProcedure')) EXEC [PrintString] @string;
Like the OBJECT_DEFINITION examples above, I have removed the intermediate lines before grabbing the screenshot just to make the images smaller but hopefully you can see from this that although it’s a little more code it does display correctly regardless of the length of the procedure or string. If you keep the above SQL as a snippit and parameterise the procedure name it’s pretty quick to use.
One final example that I will leave you with to show how [PrintString] deals with everything from empty lines to lines with more than 4000 characters.
DECLARE @InputString varchar(max ), @InputLength bigint; SET @InputString = 'This is Line 1' + CHAR(10) + 'This is line 2 after a line feed' + CHAR(13) + 'This is line 3 after a carriage return' + CHAR(10) + 'Line 4 after another line feed before an empty line' + CHAR(10) + '' + CHAR(10) + REPLICATE(CONVERT(varchar(max), '%'), 105) + CHAR(10) + 'S' + REPLICATE(CONVERT(varchar(max), '#'), 1998) + 'E' + CHAR(10) + 'S' + REPLICATE(CONVERT(varchar(max), '@'), 3998) + 'E' + CHAR(13) + CHAR(10) + 'S' + REPLICATE(CONVERT(varchar(max), '1'), 3999) + 'E' + CHAR(10) + 'S' + REPLICATE(CONVERT(varchar(max), '2'), 8000) + 'E' + CHAR(10) + 'S' + REPLICATE(CONVERT(varchar(max), '3'), 12000) + 'E' + CHAR(10) + 'S' + REPLICATE(CONVERT(varchar(max), '4'), 12004) + 'E' SET @InputLength = LEN(@InputString) PRINT '' PRINT 'Input Length: ' + CAST(@InputLength AS varchar) PRINT '' EXEC [PrintString] @InputString = @InputString, @MaxPrintLength = 4000
[PrintString] is part of Log4TSql which is an open source logging framework for SQL Server 2005+ written by Greg M Lucas on behalf of data-centric solutions ltd and licensed for public use under the GNU Lesser General Public License. You can download the latest version of this library along with all the source code from sourceforge. Please feel free to use and modify the library as you wish under the terms of the license. I am always interested in hearing how other people or organisations have put this framework to use.