How to print VARCHAR(MAX) using Print Statement?

I know it's an old question, but what I did is not mentioned here.

For me the following worked (for up to 16k chars)

DECLARE @info NVARCHAR(MAX)

--SET @info to something big

PRINT CAST(@info AS NTEXT)

If you have more than 16k chars you can combine with @Yovav's answer like this (64k should be enough for anyone ;)

    print cast( substring(@info, 1, 16000) as ntext )
    print cast( substring(@info, 16001, 16000) as ntext )
    print cast( substring(@info, 32001, 16000) as ntext )
    print cast( substring(@info, 48001, 16000) as ntext )

The following workaround does not use the PRINT statement. It works well in combination with the SQL Server Management Studio.

SELECT CAST('<root><![CDATA[' + @MyLongString + ']]></root>' AS XML)

You can click on the returned XML to expand it in the built-in XML viewer.

There is a pretty generous client side limit on the displayed size. Go to Tools/Options/Query Results/SQL Server/Results to Grid/XML data to adjust it if needed.

Here is how this should be done:

DECLARE @String NVARCHAR(MAX);
DECLARE @CurrentEnd BIGINT; /* track the length of the next substring */
DECLARE @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, LEN(@String))   
END /*End While loop*/

Taken from http://ask.sqlservercentral.com/questions/3102/any-way-around-the-print-limit-of-nvarcharmax-in-s.html

Came across this question and wanted something more simple... Try the following:

SELECT [processing-instruction(x)]=@Script FOR XML PATH(''),TYPE

I just created a SP out of Ben's great answer:

/*
---------------------------------------------------------------------------------
PURPOSE   : Print a string without the limitation of 4000 or 8000 characters.
https://stackoverflow.com/questions/7850477/how-to-print-varcharmax-using-print-statement
USAGE     : 
DECLARE @Result NVARCHAR(MAX)
SET @Result = 'TEST'
EXEC [dbo].[Print_Unlimited] @Result
---------------------------------------------------------------------------------
*/
ALTER PROCEDURE [dbo].[Print_Unlimited]
    @String NVARCHAR(MAX)
AS

BEGIN

    BEGIN TRY
    ---------------------------------------------------------------------------------

    DECLARE @CurrentEnd BIGINT; /* track the length of the next substring */
    DECLARE @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, LEN(@String))   
    END /*End While loop*/

    ---------------------------------------------------------------------------------
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage VARCHAR(4000)
        SELECT @ErrorMessage = ERROR_MESSAGE()    
        RAISERROR(@ErrorMessage,16,1)
    END CATCH
END

This proc correctly prints out VARCHAR(MAX) parameter considering wrapping:

CREATE PROCEDURE [dbo].[Print]
    @sql varchar(max)
AS
BEGIN
    declare
        @n int,
        @i int = 0,
        @s int = 0, -- substring start posotion
        @l int;     -- substring length

    set @n = ceiling(len(@sql) / 8000.0);

    while @i < @n
    begin
        set @l = 8000 - charindex(char(13), reverse(substring(@sql, @s, 8000)));
        print substring(@sql, @s, @l);
        set @i = @i + 1;
        set @s = @s + @l + 2; -- accumulation + CR/LF
    end

    return 0
END

I was looking to use the print statement to debug some dynamic sql as I imagin most of you are using print for simliar reasons.

I tried a few of the solutions listed and found that Kelsey's solution works with minor tweeks (@sql is my @script) n.b. LENGTH isn't a valid function:

--http://stackoverflow.com/questions/7850477/how-to-print-varcharmax-using-print-statement
--Kelsey
DECLARE @Counter INT
SET @Counter = 0
DECLARE @TotalPrints INT
SET @TotalPrints = (LEN(@sql) / 4000) + 1
WHILE @Counter < @TotalPrints 
BEGIN
    PRINT SUBSTRING(@sql, @Counter * 4000, 4000)
    SET @Counter = @Counter + 1
END
PRINT LEN(@sql)

This code does as commented add a new line into the output, but for debugging this isn't a problem for me.

Ben B's solution is perfect and is the most elegent, although for debugging is a lot of lines of code so I choose to use my slight modification of Kelsey's. It might be worth creating a system like stored procedure in msdb for Ben B's code which could be reused and called in one line?

Alfoks' code doesn't work unfortunately because that would have been easier.

Or simply:

PRINT SUBSTRING(@SQL_InsertQuery, 1, 8000)
PRINT SUBSTRING(@SQL_InsertQuery, 8001, 16000)

You can use this

declare @i int = 1
while Exists(Select(Substring(@Script,@i,4000))) and (@i < LEN(@Script))
begin
     print Substring(@Script,@i,4000)
     set @i = @i+4000
end

There is great function called PrintMax written by Bennett Dill.

Here is slightly modified version that uses temp stored procedure to avoid "schema polution"(idea from https://github.com/Toolien/sp_GenMerge/blob/master/sp_GenMerge.sql)

EXEC (N'IF EXISTS (SELECT * FROM tempdb.sys.objects 
                   WHERE object_id = OBJECT_ID(N''tempdb..#PrintMax'') 
                   AND type in (N''P'', N''PC''))
    DROP PROCEDURE #PrintMax;');
EXEC (N'CREATE PROCEDURE #PrintMax(@iInput NVARCHAR(MAX))
AS
BEGIN
    IF @iInput IS NULL
    RETURN;

    DECLARE @ReversedData NVARCHAR(MAX)
          , @LineBreakIndex INT
          , @SearchLength INT;

    SET @SearchLength = 4000;

    WHILE LEN(@iInput) > @SearchLength
    BEGIN
    SET @ReversedData = LEFT(@iInput COLLATE DATABASE_DEFAULT, @SearchLength);
    SET @ReversedData = REVERSE(@ReversedData COLLATE DATABASE_DEFAULT);
    SET @LineBreakIndex = CHARINDEX(CHAR(10) + CHAR(13),
                          @ReversedData COLLATE DATABASE_DEFAULT);
    PRINT LEFT(@iInput, @SearchLength - @LineBreakIndex + 1);
    SET @iInput = RIGHT(@iInput, LEN(@iInput) - @SearchLength 
                        + @LineBreakIndex - 1);
    END;

    IF LEN(@iInput) > 0
    PRINT @iInput;
END;');

DBFiddle Demo

EDIT:

Using CREATE OR ALTER we could avoid two EXEC calls:

EXEC (N'CREATE OR ALTER PROCEDURE #PrintMax(@iInput NVARCHAR(MAX))
AS
BEGIN
    IF @iInput IS NULL
    RETURN;

    DECLARE @ReversedData NVARCHAR(MAX)
          , @LineBreakIndex INT
          , @SearchLength INT;

    SET @SearchLength = 4000;

    WHILE LEN(@iInput) > @SearchLength
    BEGIN
    SET @ReversedData = LEFT(@iInput COLLATE DATABASE_DEFAULT, @SearchLength);
    SET @ReversedData = REVERSE(@ReversedData COLLATE DATABASE_DEFAULT);
    SET @LineBreakIndex = CHARINDEX(CHAR(10) + CHAR(13), @ReversedData COLLATE DATABASE_DEFAULT);
    PRINT LEFT(@iInput, @SearchLength - @LineBreakIndex + 1);
    SET @iInput = RIGHT(@iInput, LEN(@iInput) - @SearchLength + @LineBreakIndex - 1);
    END;

    IF LEN(@iInput) > 0
    PRINT @iInput;
END;');

db<>fiddle Demo

create procedure dbo.PrintMax @text nvarchar(max)
as
begin
    declare @i int, @newline nchar(2), @print varchar(max); 
    set @newline = nchar(13) + nchar(10);
    select @i = charindex(@newline, @text);
    while (@i > 0)
    begin
        select @print = substring(@text,0,@i);
        while (len(@print) > 8000)
        begin
            print substring(@print,0,8000);
            select @print = substring(@print,8000,len(@print));
        end
        print @print;
        select @text = substring(@text,@i+2,len(@text));
        select @i = charindex(@newline, @text);
    end
    print @text;
end

Uses Line Feeds and spaces as a good break point:

declare @sqlAll as nvarchar(max)
set @sqlAll = '-- Insert all your sql here'

print '@sqlAll - truncated over 4000'
print @sqlAll
print '   '
print '   '
print '   '

print '@sqlAll - split into chunks'
declare @i int = 1, @nextspace int = 0, @newline nchar(2)
set @newline = nchar(13) + nchar(10)


while Exists(Select(Substring(@sqlAll,@i,3000))) and (@i < LEN(@sqlAll))
begin
    while Substring(@sqlAll,@i+3000+@nextspace,1) <> ' ' and Substring(@sqlAll,@i+3000+@nextspace,1) <> @newline
    BEGIN
        set @nextspace = @nextspace + 1
    end
    print Substring(@sqlAll,@i,3000+@nextspace)
    set @i = @i+3000+@nextspace
    set @nextspace = 0
end
print '   '
print '   '
print '   '

My PrintMax version for prevent bad line breaks on output:

    CREATE PROCEDURE [dbo].[PrintMax](@iInput NVARCHAR(MAX))
    AS
    BEGIN
      Declare @i int;
      Declare @NEWLINE char(1) = CHAR(13) + CHAR(10);
      While LEN(@iInput)>0 BEGIN
        Set @i = CHARINDEX(@NEWLINE, @iInput)
        if @i>8000 OR @i=0 Set @i=8000
        Print SUBSTRING(@iInput, 0, @i)
        Set @iInput = SUBSTRING(@iInput, @i+1, LEN(@iInput))
      END
    END

Here's another version. This one extracts each substring to print from the main string instead of taking reducing the main string by 4000 on each loop (which might create a lot of very long strings under the hood - not sure).

CREATE PROCEDURE [Internal].[LongPrint]
    @msg nvarchar(max)
AS
BEGIN

    -- SET NOCOUNT ON reduces network overhead
    SET NOCOUNT ON;

    DECLARE @MsgLen int;
    DECLARE @CurrLineStartIdx int = 1;
    DECLARE @CurrLineEndIdx int;
    DECLARE @CurrLineLen int;   
    DECLARE @SkipCount int;

    -- Normalise line end characters.
    SET @msg = REPLACE(@msg, char(13) + char(10), char(10));
    SET @msg = REPLACE(@msg, char(13), char(10));

    -- Store length of the normalised string.
    SET @MsgLen = LEN(@msg);        

    -- Special case: Empty string.
    IF @MsgLen = 0
    BEGIN
        PRINT '';
        RETURN;
    END

    -- Find the end of next substring to print.
    SET @CurrLineEndIdx = CHARINDEX(CHAR(10), @msg);
    IF @CurrLineEndIdx BETWEEN 1 AND 4000
    BEGIN
        SET @CurrLineEndIdx = @CurrLineEndIdx - 1
        SET @SkipCount = 2;
    END
    ELSE
    BEGIN
        SET @CurrLineEndIdx = 4000;
        SET @SkipCount = 1;
    END     

    -- Loop: Print current substring, identify next substring (a do-while pattern is preferable but TSQL doesn't have one).
    WHILE @CurrLineStartIdx < @MsgLen
    BEGIN
        -- Print substring.
        PRINT SUBSTRING(@msg, @CurrLineStartIdx, (@CurrLineEndIdx - @CurrLineStartIdx)+1);

        -- Move to start of next substring.
        SET @CurrLineStartIdx = @CurrLineEndIdx + @SkipCount;

        -- Find the end of next substring to print.
        SET @CurrLineEndIdx = CHARINDEX(CHAR(10), @msg, @CurrLineStartIdx);
        SET @CurrLineLen = @CurrLineEndIdx - @CurrLineStartIdx;

        -- Find bounds of next substring to print.              
        IF @CurrLineLen BETWEEN 1 AND 4000
        BEGIN
            SET @CurrLineEndIdx = @CurrLineEndIdx - 1
            SET @SkipCount = 2;
        END
        ELSE
        BEGIN
            SET @CurrLineEndIdx = @CurrLineStartIdx + 4000;
            SET @SkipCount = 1;
        END
    END
END

This should work properly this is just an improvement of previous answers.

DECLARE @Counter INT
DECLARE @Counter1 INT
SET @Counter = 0
SET @Counter1 = 0
DECLARE @TotalPrints INT
SET @TotalPrints = (LEN(@QUERY) / 4000) + 1
print @TotalPrints 
WHILE @Counter < @TotalPrints 
BEGIN
-- Do your printing...
print(substring(@query,@COUNTER1,@COUNTER1+4000))

set @COUNTER1 = @Counter1+4000
SET @Counter = @Counter + 1
END

If the source code will not have issues with LF to be replaced by CRLF, No debugging is required by following simple codes outputs.

--http://stackoverflow.com/questions/7850477/how-to-print-varcharmax-using-print-statement
--Bill Bai
SET @SQL=replace(@SQL,char(10),char(13)+char(10))
SET @SQL=replace(@SQL,char(13)+char(13)+char(10),char(13)+char(10) )
DECLARE @Position int 
WHILE Len(@SQL)>0 
BEGIN
SET @Position=charindex(char(10),@SQL)
PRINT left(@SQL,@Position-2)
SET @SQL=substring(@SQL,@Position+1,len(@SQL))
end; 

If someone interested I've ended up as generating a text file with powershell, executing scalar code:

$dbconn = "Data Source=sqlserver;" + "Initial Catalog=DatabaseName;" + "User Id=sa;Password=pass;"
$conn = New-Object System.Data.SqlClient.SqlConnection($dbconn)
$conn.Open()

$cmd = New-Object System.Data.SqlClient.SqlCommand("
set nocount on

DECLARE @sql nvarchar(max) = ''

SELECT 
    @sql += CHAR(13) + CHAR(10) + md.definition + CHAR(13) + CHAR(10) + 'GO'
FROM sys.objects AS obj
join sys.sql_modules AS md on md.object_id = obj.object_id
join sys.schemas AS sch on sch.schema_id = obj.schema_id
where obj.type = 'TR'


select @sql
", $conn)
$data = [string]$cmd.ExecuteScalar()


$conn.Close()

$data | Out-File -FilePath "C:\Users\Alexandru\Desktop\bigstring.txt"

This script it's for getting a big string with all the triggers from the DB.

I read through the above and tried my own simplified version that outputs the procedure text with the correct vertical spacing. Variable declares

DECLARE @tp     as varchar(100)
DECLARE @def    as nvarchar(MAX)
DECLARE @prt1   as nvarchar(4000)
DECLARE @prt2   as nvarchar(MAX)
DECLARE @pos1   as int
DECLARE @pos2   as int

in my implementation I am looking to output a list of procedures listed in a prior query and held in a temp table. I then loop through them setting the variable @tp to the procedure I want to output. I set the variable @def to the procedure definition

    SET @def = (SELECT  CAST(m.definition as nvarchar(MAX))
                FROM    sys.objects o
                            LEFT JOIN sys.sql_modules m
                            on m.object_id = o.object_id
                WHERE   o.name = @tp) 

then I use a simple "carving loop" to size down the output to be compatible with the SSMS message output window and print statement

    WHILE (Len(@def) > 4000)
      BEGIN
        SET @pos1 = CHARINDEX(CHAR(13), @Def,3600)
        SET @prt1 = SUBSTRING(@def,1,@pos1-1)
        SET @pos2 = LEN(@Def)
        SET @prt2 = SUBSTRING(@def, @pos1+2, @pos2-@pos1)
        SET @def = @prt2
        print @prt1 
      END
    Print @def

once it is below the 4k character limit I simply output what is remaining. We bypass the intervening CR/LF as that is supplied via the Print command. It is nothing revolutionary but has the benefit of being straight-forward and quite easy to read.