How do I view the full content of a text or varchar(MAX) column in SQL Server 2008 Management Studio?

SSMS only allows unlimited data for XML data. This is not the default and needs to be set in the options.

One trick which might work in quite limited circumstances is simply naming the column in a special manner as below so it gets treated as XML data.

DECLARE @S varchar(max) = 'A'

SET @S =  REPLICATE(@S,100000) + 'B' 

SELECT @S as [XML_F52E2B61-18A1-11d1-B105-00805F49916B]

In SSMS (at least versions 2012 to current of 18.3) this displays the results as below

Clicking on it opens the full results in the XML viewer. Scrolling to the right shows the last character of B is preserved,

However this does have some significant problems. Adding extra columns to the query breaks the effect and extra rows all become concatenated with the first one. Finally if the string contains characters such as < opening the XML viewer fails with a parsing error.

A more robust way of doing this that avoids issues of SQL Server converting < to < etc or failing due to these characters is below (credit Adam Machanic here).

DECLARE @S varchar(max)

SELECT @S = ''

SELECT @S = @S + '
' + OBJECT_DEFINITION(OBJECT_ID) FROM SYS.PROCEDURES

SELECT @S AS [processing-instruction(x)] FOR XML PATH('')

I was able to get this to work...

SELECT CAST('<![CDATA[' + LargeTextColumn + ']]>' AS XML) FROM TableName;

One work-around is to right-click on the result set and select "Save Results As...". This exports it to a CSV file with the entire contents of the column. Not perfect but worked well enough for me.