How to print very long strings and procedures

by Greg M Lucas on 23 November 2011

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.

{ 3 comments }

Aaron Bertrand November 23, 2011 at 14:48

Nice post, and handy procedure. In my defense, I was advocating using OBJECT_DEFINITION() for searching (which doesn’t necessarily involve SSMS at all), not for printing in full. Even the engine team can’t compensate for all of the issues with SSMS. :-)

Greg M Lucas November 23, 2011 at 22:18

Thanks Aaron. I wasn’t trying to suggest that you were advocating OBJECT_DEFINITION() as a good option for printing modules, although inevitably that is how some people might end up using it – which is when they’ll hit the 8000 character limititation in SSMS. Of course, in an ideal world, perhaps developers shouldn’t be writing such monolithic procedures as it makes proper unit testing almost impossible but that’s a whole other story :-). I will review the content of this post to ensure that it reflects the intent of what you say in yours. Thanks again.

Oleksandr Dubelewskyj June 20, 2012 at 06:54

It works perfect for me. Thanks!

Comments on this entry are closed.

{ 1 trackback }

Previous post:

Next post: