How to print VARCHAR MAX using Print Statement

Question

I have a code which is   DECLARE  Script VARCHAR MAX   SELECT  Script   definition FROM manged sys all sql modules sq where sq object id    SELECT object id from managed sys objects  Where type    P  and Name    usp gen data    Declare  Pos int  SELECT   pos CHARINDEX CHAR 13  CHAR 10   script 7500   PRINT SUBSTRING  Script 1  Pos   PRINT SUBSTRING  script  pos 8000    The length of the Script is around 10 000 Characters and Since I am using print Statement which can hold only max of 8000  So I am using two print statements   The problem is when I have a script which is of say 18000 characters then I used to use 3 print statements   So Is there a way that I could set the number of print statements depending on the length of the script

User · Answer

Or simply   PRINT SUBSTRING  SQL InsertQuery  1  8000  PRINT SUBSTRING  SQL InsertQuery  8001  16000

User · Answer

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

User · Answer

You could do a WHILE loop based on the count on your script length divided by 8000   EG   DECLARE  Counter INT SET  Counter   0 DECLARE  TotalPrints INT SET  TotalPrints    LEN  script    8000    1 WHILE  Counter  lt   TotalPrints  BEGIN     -- Do your printing        SET  Counter    Counter   1 END

User · Answer

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  lt  LEN  sqlAll   begin     while Substring  sqlAll  i 3000  nextspace 1   lt  gt      and Substring  sqlAll  i 3000  nextspace 1   lt  gt   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

User · Answer

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  lt   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

User · Answer

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  lt   TotalPrints  BEGIN -- Do your printing    print substring  query  COUNTER1  COUNTER1 4000    set  COUNTER1    Counter1 4000 SET  Counter    Counter   1 END

User · Answer

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  lt   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

User · Answer

I know it s an old question  but what I did is not mentioned here   For me the following worked   DECLARE  info NVARCHAR MAX   --SET  info to something big  PRINT CAST  info AS NTEXT

User · Answer

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   gt  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

User · Answer

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  lt   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

User · Answer

The following workaround does not use the PRINT statement   It works well in combination with the SQL Server Management Studio   SELECT CAST   lt root gt  lt   CDATA      MyLongString       gt  lt  root gt   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

User · Answer

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

User · Answer

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  gt 0  BEGIN SET  Position charindex char 10   SQL  PRINT left  SQL  Position-2  SET  SQL substring  SQL  Position 1 len  SQL   end

User · Answer

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   gt  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

User · Answer

You can use this  declare  i int   1 while Exists Select Substring  Script  i 4000    and   i  lt  LEN  Script   begin      print Substring  Script  i 4000       set  i    i 4000 end

User · Answer

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   gt   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   gt  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   gt   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   gt  0     PRINT  iInput  END       db lt  fiddle Demo

User · Answer

Came across this question and wanted something more simple    Try the following   SELECT  processing-instruction x    Script FOR XML PATH     TYPE

[sql] How to print VARCHAR(MAX) using Print Statement?

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to tsql

Examples related to sql-server-2008