How to show more than 8000 chars in SSMS output

If you ever tried to display more than 8000 characters column such as varchar(MAX) column value in SSMS output in text mode then you will quickly realize that you hit the wall. SSMS truncates anything more than 8000 chars (approx. limit) for your column value. There is no easy way in SSMS to show more than 8000 chars. Technically Grid mode allows you to do that but again it trims all new lines in your string making it useless for copy/paste scenarios.

Here is very simple trick … Basically if you convert string to XML by wrapping around CDATA tag of XML then you get clickable link as below and it preserves text formatting.

SQL Query

SELECT top 10 name, type, cast('<![CDATA[' + OBJECT_DEFINITION(x.object_id) + ']]>' as XML) as col1
FROM sys.views x

This technique doesn’t face limitation of 8000 characters in Text output mode or Newline chars removal of Grid Mode.

Limitations:
You may get invalid XML character error if you have some invalid XML character such as vertical tab (e.g. char(11)) 

Hope this helps 🙂