Display long SQL text within Management Studio

Did you ever try to display long varchar's in Management Studio query result ? If you use the default option "Display result to grids" (as I do), it is a pain because:

1. if the text is very long, it may be truncated in the grid
2. if your text contains line-feeds, they are not displayed in the grid

My tip for this issue is to convert your text into XML, so that when you click on the grid cell containing your text, it is well displayed.

The expression to convert into XML is the following. Pay attention to the special characters that must be escaped before casting to XML type !

CAST(REPLACE(REPLACE(YourVarcharColumn, '&', '&amp;'), '<', '&lt;') AS XML)

Let's take an example:

/* ******************* */
/* CREATE SAMPLE TABLE */
/* ******************* */

CREATE TABLE OneTable ( Id INT IDENTITY(1, 1), LogDate DATETIME, TextColumn VARCHAR(4000) )


/* ******************************** */
/* FILL SAMPLE TABLE WITH SOME DATA */
/* ******************************** */

INSERT INTO OneTable ( LogDate, TextColumn ) VALUES
(GETDATE(), 'Mignonne, allons voir si la rose
Qui ce matin avoit desclose
Sa robe de pourpre au Soleil,
A point perdu ceste vesprée
Les plis de sa robe pourprée,
Et son teint au vostre pareil.')

INSERT INTO OneTable ( LogDate, TextColumn ) VALUES
(GETDATE(), 'Las ! voyez comme en peu d''espace,
Mignonne, elle a dessus la place
Las ! las ses beautez laissé cheoir !
Ô vrayment marastre Nature,
Puis qu''une telle fleur ne dure
Que du matin jusques au soir !')

INSERT INTO OneTable ( LogDate, TextColumn ) VALUES
(GETDATE(), 'Donc, si vous me croyez, mignonne,
Tandis que vostre âge fleuronne
En sa plus verte nouveauté,
Cueillez, cueillez vostre jeunesse :
Comme à ceste fleur la vieillesse
Fera ternir vostre beauté.')


/* ************************* */
/* SELECT DATA THE USUAL WAY */
/* ************************* */

SELECT Id, LogDate, TextColumn
FROM OneTable


/* **************************** */
/* SELECT DATA WITH CAST AS XML */
/* **************************** */

SELECT Id, LogDate, CAST(REPLACE(REPLACE(TextColumn, '&', '&amp;'), '<', '&lt;') AS XML) AS TextColumn
FROM OneTable


/* ************************* */
/* CLEAN UP THE SAMPLE STUFF */
/* ************************* */

DROP TABLE OneTable

The first select, using varchar type, will return something like that:

Even if you can resize the column, admit this is not very user-friendly.

The second select, using XML cast, will return something like that:

Well... you will tell me this not more sexy... But if you click on the XML cells, it will display the exact text (except escape characters):

If you need to use it frequently, I recommend to create a scalar-valued function to do the job, which avoids you to type the boring replace/replace/cast expression !

CREATE FUNCTION CastVarcharToXml ( @Input VARCHAR(MAX) )
RETURNS XML
AS
BEGIN
 RETURN CAST(REPLACE(REPLACE(@Input, '&', '&amp;'), '<', '&lt;') AS XML)
END

Clearly, the limitations are :

1. you have to click on every text you want to full-display out-of the grid
2. the characters you had to escape (< and &) will be printed as escaped characters

Anyway, this might help you if you log long texts in your db, like .Net exceptions with call stack !