Printing more than 8000 characters in text output

Question

There is a limit to the print output on MS SQL server (I'm using SQL Server 2014).

I've got my Maximum number of characters displayed in each column set to 8192 (the max). When I try to set a variable to a long string and print it, it gets truncated. With output set to text:

declare @text nvarchar(max)
set @text = (
             select definition
               from sys.sql_modules
              where object_id = object_id(N'NameOfALargeStoredProcedure')
            )
print @text

This will return about 4K of the characters. If I change @text to varchar (instead of nvarchar) I get around 8K characters.

The definition of the procedure is much larger than 8K. I need to print the entire text of a string that is larger than 8K.

I know SQL Server has a built-in script object feature. What I need to print may not be a database object: I need to print an arbitrary string.

Still has the limitation. Additionally, it lists a column header where I don't want one. I need a print. — Brian Stork, Sep 17 '15 at 17:46
http://stackoverflow.com/questions/7850477/how-to-print-varcharmax-using-print-statement — Lukasz Szozda, Sep 17 '15 at 17:47
Thank you. I looked for an answer and didn't find that. I don't like the loop. I'll make one that doesn't need a loop. — Brian Stork, Sep 17 '15 at 17:49
This limitation is only in SSMS. It is NOT a limitation of sql server. Why do you need to print such long strings in SSMS? If this is an external application there is no limit to string length. — Sean Lange, Sep 17 '15 at 18:29

score 2 · Answer 1 · answered Sep 17 '15 at 18:50

Sometimes I have to generate very large scripts, so I had to solve the same problem. This is my solution:

Create a select where you add a line break at the end and let it be XML. The query options (right click in your query window, section "grid") allow to specify the maximum size of an XML in grid view, which is almost unlimited (limits by your hardware).

After clicking the XML you can copy and paste its content.

Just try it:

SELECT name + CHAR(13)+CHAR(10)
FROM sys.objects
FOR XML PATH(''),ROOT('x')

Printing more than 8000 characters in text output

1 Answers1