How to display long text in SSMS

Published 25 October 09 10:15 PM | MartinBell
Displaying text in SQL Server Management Studio has never been great!! Writing large amounts of data to the screen that will often be unread does not help performance and the user experience could be degraded. When you display results to text the default maximum size of characters that you can display in a column is 256 characters. This can be increased to 8192 characters which for most people will be more than enough for everyday use. You can change the size by going to the Tools/Option menu and opening the Query Results branch on the tree control. Then under the Results to Text leaf is the the “Maximum number of characters in a column” value.



If you are displaying large text data, it is quite likely that your data will have line feeds in it. When displaying text results this can be a nuisance as it will mess up the alignment of subsequent columns in your results. To get around this I usually display the results in grid mode. If you do this you can set the maximum number of characters by changing the Maximum Caracters Retrieved for Non XML data on the options screen for “Results to Grid”.




The default value for this is 65535, but you can change this to be lower. Unfortunately whilst writing this post I came across a bug in SSMS. This is that when you cut/paste data from a column in grid mode, only 43679 characters will be transferred. I raised this as a
Connect item.

The other issue with “Results to Grid” is that it removes any linefeed characters from the results. For example if you have the following script:

IF OBJECT_ID('dbo.prc_LongStringLength') IS NOT NULL
            DROP PROCEDURE prc_LongStringLength
;
GO

DECLARE
@procdefn varchar(MAX
);

SET @procdefn = 
'CREATE PROCEDURE dbo.prc_LongStringLength
AS
BEGIN
            SELECT LEN('''
+ REPLICATE ( CAST('A' AS varchar(MAX)), 65535) +
''');
END'


EXEC ( @procdefn
);

SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.prc_LongStringLength'
));

The results will not only be truncated they will be a single line like the following:

CREATE PROCEDURE dbo.prc_LongStringLengths  AS  BEGIN   SELECT LEN('AAAAAAAA

When this is the definition of a stored procedure it is not very useful! So what can you do to get the full definition? If you noticed on the Options of “Result to Grid”, you can set the number of characters to be returned for XML data separately. The default is 2MB, but you can choose an unlimited maximum. When using “Result to Grid”, SSMS will display XML data as a clickable link, which when clicked will open a new window.

You can make use of this by casting the text you want to display as XML e.g.

SELECT CAST(OBJECT_DEFINITION(OBJECT_ID('dbo.prc_LongStringLength')) AS XML);


This will allow you to click on the link and view the text. Unfortunately this does have some limitations for instance if this was the procedure definition: 

IF OBJECT_ID('dbo.prc_LongStringLength') IS NOT NULL
                DROP PROCEDURE prc_LongStringLength
;
GO

DECLARE
@procdefn varchar(MAX
);

SET @procdefn = 
'CREATE PROCEDURE dbo.prc_LongStringLength
AS
BEGIN
                IF 1 < 2
                                SELECT LEN('''
+ REPLICATE ( CAST('A' AS varchar(MAX)), 65535) +
''');
END'
;

EXEC ( @procdefn
);

SELECT CAST(OBJECT_DEFINITION(OBJECT_ID('dbo.prc_LongStringLength')) AS XML);


You will get the error:

Msg 9455, Level 16, State 1, Line 13
XML parsing: line 4, character 8, illegal qualified name character

Another way of displaying the data is to use the FOR XML clause e.g.

IF OBJECT_ID('dbo.prc_LongStringLength') IS NOT NULL
                DROP PROCEDURE prc_LongStringLength;
GO

DECLARE @procdefn varchar(MAX);

SET @procdefn =  'CREATE PROCEDURE dbo.prc_LongStringLength
AS
BEGIN
              SELECT LEN(''' + REPLICATE ( CAST('&<>' AS varchar(MAX)), 3) + ''');
END' ; 

EXEC ( @procdefn );  

SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.prc_LongStringLength'))  FOR XML PATH(''), TYPE

This will also produce a clickable link, that will open in a new tab. If you looked at the above example you will see that it will perform entitisation on the XML reserved characters so instead of displaying the ampersand character this is substituted with &amp; and the greater than is substituted with &gt; less than is substituted with &lt; i.e.

CREATE PROCEDURE dbo.prc_LongStringLength
AS
BEGIN
                                SELECT LEN('&amp;&lt;&gt;&amp;&lt;&gt;&amp;&lt;&gt;');
END

So how do you overcome this? You can use XML EXPLICIT and specify the text column with a CDATA directive.

SELECT  1 as Tag,
        0 as Parent,
        OBJECT_DEFINITION(OBJECT_ID('dbo.prc_LongStringLength')) as [TextData!1!!CDATA]
FOR XML EXPLICIT ;

This will give the following results:

<TextData><![CDATA[CREATE PROCEDURE dbo.prc_LongStringLength
AS
BEGIN
                                SELECT LEN('&<>&<>&<>');
END]]></TextData>

This will allow you to see text data including line feeds, but it a bit ugly! A colleague at work
Rhys Jones found this interesting feature:

IF OBJECT_ID('dbo.prc_LongStringLength') IS NOT NULL
                DROP PROCEDURE prc_LongStringLength
;
GO

DECLARE @procdefn varchar(MAX); 
SET @procdefn =  'CREATE PROCEDURE dbo.prc_LongStringLength
AS
BEGIN
                                SELECT LEN(''' + REPLICATE ( CAST('A' AS varchar(MAX)), 65535) + ''');
END' ;

EXEC ( @procdefn ); 

SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.prc_LongStringLength')) AS [XML_F52E2B61-18A1-11d1-B105-00805F49916B];


This will create a clickable link, this does not always work if you have multiple columns e.g.


SELECT
1 as Id,
             
OBJECT_DEFINITION(OBJECT_ID('dbo.prc_LongStringLength')) AS [XML_F52E2B61-18A1-11d1-B105-00805F49916B];


it does not work. Also if you have one of the reserved characters you will get an error message when you click on the link, although you can still cut and paste the definition in full into another window and it will not be truncated e.g.


IF
OBJECT_ID('dbo.prc_LongStringLength') IS NOT NULL
                DROP PROCEDURE prc_LongStringLength;
GO

DECLARE @procdefn varchar(MAX); 

SET @procdefn =  'CREATE PROCEDURE dbo.prc_LongStringLength
AS
BEGIN
                                SELECT LEN(''' + REPLICATE ( CAST('<' AS varchar(MAX)), 65535) + ''');
END' ; 

EXEC ( @procdefn ); 

SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.prc_LongStringLength')) AS [XML_F52E2B61-18A1-11d1-B105-00805F49916B];

Clicking the link will give you:



None of these solutions are great so I raised a feature request on Connect so that varchar(MAX) or nvarcar(MAX) data can be displayed in the same way as XML without having to code around it.

Since writing this MVP Adam Mechanic has added the a comment to the connect item pointing out that you can use a process-instruction to avoid entitisation and gain the benefits of a converting to XML

SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.prc_LongStringLength')) AS [processing-instruction(x)] FOR XML PATH('')

Filed under:

Comments

# Dew Drop – October 26, 2009 | Alvin Ashcraft's Morning Dew said on October 26, 2009 12:03 PM:

Pingback from  Dew Drop &#8211; October 26, 2009 | Alvin Ashcraft&#039;s Morning Dew

# rmjcsltd said on December 10, 2009 09:07 PM:

How could you forget to mention the nerdy fact that F52E2B61-18A1-11d1-B105-00805F49916B is the CLSID for an IXMLDocument?

Rhys ;)

# MartinBell said on December 11, 2009 09:01 AM:

Yep I missed that out! Maybe I should get it tattooed somewhere? Suggestions to...

# Twitter Trackbacks for Bookmarked: How to display long text in SSMS - Martin Bell UK SQL Server MVP: [sqlblogcasts.com] on Topsy.com said on May 8, 2010 04:07 AM:

Pingback from  Twitter Trackbacks for                 Bookmarked: How to display long text in SSMS - Martin Bell UK SQL Server MVP:         [sqlblogcasts.com]        on Topsy.com

This Blog

SQL Blogs

Syndication