How to preview large string

Often you need to debug dynamic SQL statement. Simplest way to do this is to print statement. Because of 8000 char limit (400 for unicode), it is hard to do in "standard way".

here is an example:

DECLARE @Very_big_string NVARCHAR(MAX)= '' ;
WITH    c AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY o1.object_id, o2.object_id ) RN
               FROM     sys.objects o1
                        CROSS JOIN sys.objects o2
             )
    SELECT TOP 9999
            @Very_big_string = @Very_big_string + 
    REPLICATE(0,4 - LEN(CAST(rn AS NVARCHAR)))
            + CAST(rn AS NVARCHAR) + CHAR(13)
    FROM    c    

PRINT @Very_big_string 

Last result will be 0800 - 4 chars  for digits and one for end line - char(13) (4+1)*5=4000

There are some methods to print string by UDF etc. I propose simpler method: print XML which will contain your string (xml is not limited by char, by size in SSMS settings only - default is 2MB
So here is an example for this string:

DECLARE @Very_big_string NVARCHAR(MAX)= '' ;
WITH    c AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY o1.object_id, o2.object_id ) RN
               FROM     sys.objects o1
                        CROSS JOIN sys.objects o2
             )
    SELECT TOP 9999
            @Very_big_string = @Very_big_string + 
    REPLICATE(0,4 - LEN(CAST(rn AS NVARCHAR)))
            + CAST(rn AS NVARCHAR) + CHAR(13)
    FROM    c    

SELECT CAST(''+@Very_big_string+'' as XML)

Here you can preview your string in XML viewer.

Disadvantage of this method is that all standard XML symbols like <,>,! etc are changed to ampersand sequences (eg > is changed to &dt;)