[sql-server] How do you view ALL text from an ntext or nvarchar(max) in SSMS?

How do you view ALL text from an NTEXT or NVARCHAR(max) in SQL Server Management Studio? By default, it only seems to return the first few hundred characters (255?) but sometimes I just want a quick way of viewing the whole field, without having to write a program to do it. Even SSMS 2012 still has this problem :(

I have written an add-in for SSMS and this problem is fixed there. You can use one of 2 ways:

you can use "Copy current cell 1:1" to copy original cell data to clipboard:

http://www.ssmsboost.com/Features/ssms-add-in-copy-results-grid-cell-contents-line-with-breaks

Or, alternatively, you can open cell contents in external text editor (notepad++ or notepad) using "Cell visualizers" feature: http://www.ssmsboost.com/Features/ssms-add-in-results-grid-visualizers

(feature allows to open contents of field in any external application, so if you know that it is text - you use text editor to open it. If contents is binary data with picture - you select view as picture. Sample below shows opening a picture):

I was successful with this method today. It's similar to the other answers in that it also converts the contents to XML, just using a different method. As I didn't see FOR XML PATH mentioned amongst the answers, I thought I'd add it for completeness:

SELECT [COL_NVARCHAR_MAX]
  FROM [SOME_TABLE]
  FOR XML PATH(''), ROOT('ROOT')

This will deliver a valid XML containing the contents of all rows, nested in an outer <ROOT></ROOT> element. The contents of the individual rows will each be contained within an element that, for this example, is called <COL_NVARCHAR_MAX>. The name of that can be changed using an alias via AS.

Special characters like &, < or > or similar will be converted to their respective entities. So you may have to convert <, > and & back to their original character, depending on what you need to do with the result.

EDIT

I just realized that CDATA can be specified using FOR XML too. I find it a bit cumbersome though. This would do it:

SELECT 1 as tag, 0 as parent, [COL_NVARCHAR_MAX] as [COL_NVARCHAR_MAX!1!!CDATA]
  FROM [SOME_TABLE]
  FOR XML EXPLICIT, ROOT('ROOT')

If you only have to view it, I've used this:

print cast(dbo.f_functiondeliveringbigformattedtext(seed) as text)

The end result is that I get line feeds and all the content in the messages window of SMSS. Of course, it only allows for a single cell - if you want to do a single cell from a number of rows, you could do this:

declare @T varchar(max)=''
select @T=@T
       + isnull(dbo.f_functiondeliveringbigformattedtext(x.a),'NOTHINGFOUND!')
       + replicate(char(13),4)
from x -- table containing multiple rows and a value in column a
print @T

I use this to validate JSON strings generated by SQL code. Too hard to read otherwise!

Alternative 1: Right Click to copy cell and Paste into Text Editor (hopefully with utf-8 support)

Alternative 2: Right click and export to CSV File

Alternative 3: Use SUBSTRING function to visualize parts of the column. Example:

SELECT SUBSTRING(fileXml,2200,200) FROM mytable WHERE id=123456

I was able to get the full text (99,208 chars) out of a NVARCHAR(MAX) column by selecting (Results To Grid) just that column and then right-clicking on it and then saving the result as a CSV file. To view the result open the CSV file with a text editor (NOT Excel). Funny enough, when I tried to run the same query, but having Results to File enabled, the output was truncated using the Results to Text limit.

The work-around that @MartinSmith described as a comment to the (currently) accepted answer didn't work for me (got an error when trying to view the full XML result complaining about "The '[' character, hexadecimal value 0x5B, cannot be included in a name").

PowerShell Alternative

This is an old post and I read through the answers. Still, I found it a bit too painful to output multi-line large text fields unaltered from SSMS. I ended up writing a small C# program for my needs, but got to thinking it could probably be done using the command line. Turns out, it is fairly easy to do so with PowerShell.

Start by installing the SqlServer module from an administrative PowerShell.

Install-Module -Name SqlServer

Use Invoke-Sqlcmd to run your query:

$Rows = Invoke-Sqlcmd -Query "select BigColumn from SomeTable where Id = 123" `
    -As DataRows -MaxCharLength 1000000 -ConnectionString $ConnectionString

This will return an array of rows that you can output to the console as follows:

$Rows[0].BigColumn

Or output to a file as follows:

$Rows[0].BigColumn | Out-File -FilePath .\output.txt -Encoding UTF8

The result is a beautiful un-truncated text written to a file for viewing/editing. I am sure there is a similar command to save back the text to SQL Server, although that seems like a different question.

EDIT: It turns out that there was an answer by @dvlsc that described this approach as a secondary solution. I think because it was listed as a secondary answer, is the reason I missed it in the first place. I am going to leave my answer which focuses on the PowerShell approach, but wanted to at least give credit where it was due.

Quick trick-

SELECT CAST('<A><![CDATA[' + CAST(LogInfo as nvarchar(max)) + ']]></A>' AS xml)
FROM Logs
WHERE IDLog = 904862629

Return data as XML

SELECT CONVERT(XML, [Data]) AS [Value]
FROM [dbo].[FormData]
WHERE [UID] LIKE '{my-uid}'

Make sure you set a reasonable limit in the SSMS options window, depending on the result you're expecting.

This will work if the text you're returning doesn't contain unencoded characters like & instead of & that will cause the XML conversion to fail.

Returning data using PowerShell

For this you will need the PowerShell SQL Server module installed on the machine on which you'll be running the command.

If you're all set up, configure and run the following script:

Invoke-Sqlcmd -Query "SELECT [Data] FROM [dbo].[FormData] WHERE [UID] LIKE '{my-uid}'" -ServerInstance "database-server-name" -Database "database-name" -Username "user" -Password "password" -MaxCharLength 10000000 | Out-File -filePath "C:\db_data.txt"

Make sure you set the -MaxCharLength parameter to a value that suits your needs.

The easiest way to quickly view large varchar/text column:

declare @t varchar(max)

select @t = long_column from table

print @t