How to print more than 8,000 characters at a time to the SSMS Message window, without compromising text formatting?

Question

Whenever I write dynamic SQL, I typically include a PRINT @DynamicSQL statement in a comment right above the EXEC sp_ExecuteSQL @DynamicSQL statement so that the dynamic SQL can be easily read and debugged when needed.

I realized the PRINT statement has a limit of 8,000 characters before it truncates the string. I know I can loop over my @DynamicSQL variable the number of times 8,000 divides into it's length and print each 8,000 chunk per iteration, but then you lose the formatting where a statement in @DynamicSQL is across two chunks, which kind of defeats my purpose.

Has anyone found a better way to preserve formatting while printing a string more than 8,000 characters?...perhaps through a custom function or procedure?

Answer 1

I have not personally used this technique, but you could try LongPrint. From that post:

This very simple procedure is designed to overcome the limitation in the SQL print command that causes it to truncate strings longer than 8000 characters.

It will print the text passed to it in substrings smaller than 8000 characters. If there are carriage returns (CRs) in the text, it will break up the substrings at the carriage returns and the printed version will exactly reflect the string passed.

If there are insufficient CRs in the text, it will print it out in blocks of 8000 characters with an extra carriage return at that point.

If it is passed a null value, it will do virtually nothing.

I developed a need to display very lengthy strings while trying to debug a script that generated a very long dynamic SQL section. When I did not instantly find a script to do this on SQLServerCentral.com I decided it would be faster to write one myself than search the broader internet.

Copying the code from that post:

CREATE PROCEDURE [dbo].[LongPrint]
      @String NVARCHAR(MAX)

AS

/*
Example:

exec LongPrint @string =
'This String
Exists to test
the system.'

*/

/* This procedure is designed to overcome the limitation
in the SQL print command that causes it to truncate strings
longer than 8000 characters (4000 for nvarchar).

It will print the text passed to it in substrings smaller than 4000
characters.  If there are carriage returns (CRs) or new lines (NLs in the text),
it will break up the substrings at the carriage returns and the
printed version will exactly reflect the string passed.

If there are insufficient line breaks in the text, it will
print it out in blocks of 4000 characters with an extra carriage
return at that point.

If it is passed a null value, it will do virtually nothing.

NOTE: This is substantially slower than a simple print, so should only be used
when actually needed.
 */

DECLARE
               @CurrentEnd BIGINT, /* track the length of the next substring */
               @offset tinyint /*tracks the amount of offset needed */


set @string = replace(  replace(@string, char(13) + char(10), char(10))   , char(13), char(10))

WHILE LEN(@String) > 1
BEGIN


    IF CHARINDEX(CHAR(10), @String) between 1 AND 4000
    BEGIN

           SET @CurrentEnd =  CHARINDEX(char(10), @String) -1
           set @offset = 2
    END
    ELSE
    BEGIN
           SET @CurrentEnd = 4000
            set @offset = 1
    END   


    PRINT SUBSTRING(@String, 1, @CurrentEnd) 

    set @string = SUBSTRING(@String, @CurrentEnd+@offset, 1073741822)   

END /*End While loop*/