How do you view ALL text from an ntext or nvarchar(max) in SSMS?

Question

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.

Catchpole

possible duplicate of how to get the full resultset from SSMS – Kerato

Answer 1

In newer versions of SSMS it can be configured in the (Query/Query Options/Results/Grid/Maximum Characters Retrieved) menu:

---

Old versions of SSMS

Options (Query Results/SQL Server/Results to Grid Page)

To change the options for the current queries, click Query Options on the Query menu, or right-click in the SQL Server Query window and select Query Options.

...

Maximum Characters Retrieved
Enter a number from 1 through 65535 to specify the maximum number of characters that will be displayed in each cell.

Maximum is, as you see, 64k. The default is much smaller.

BTW Results to Text has even more drastic limitation:

Maximum number of characters displayed in each column
This value defaults to 256. Increase this value to display larger result sets without truncation. The maximum value is 8,192.

Dorinedorion

As Remus wrote in his answer, there are limitations, because maximum storage size for data type in column defined with MAX is 2 GB so there has to be some limitation because it would be cumbersome to display all of that data in SSMS. – Hubbub

@IvanG - Not really. It could just be hyperlinked in the same way as XML is and clicking on it would open it in a new window. The work around I normally use is to cast it to XML as here. XML data can be set to allow unlimited length. – Kerato

@MartinSmith Interesting, so it is possible to make SSMS display such large amount of data... – Hubbub

The main problem was that I was viewing results as text and didn't realise that viewing in a grid gave me more data. I presumed it was only the display format which changed (view as text renders faster, so I tend to leave it on this). Hence I was only seeing 256 bytes! Thanks for your answer :) – Catchpole

Only works if the number of characters < 65535, which is a long way short of the ntext maximum. – Jory

@IvanG even 'Result to File' suffers from this limit. – Scarcity

Sorry, my mistake, SSMS debug window showes partial data, I had to click on the magnifying glass to see the entire string. Thank you very much for your help! – Fi

could not see all the text in the query results as grid, but could copy and paste into another app and see all the text. – Exeat

this answer is more relevant and should be the answer maybe? https://mcmap.net/q/75617/-how-do-you-view-all-text-from-an-ntext-or-nvarchar-max-in-ssms – Mcripley

Limit is much higher in latest version of SSMS. Good solution! – Musculature

New Non XML data limit is now 2,097,152 characters, new XML data limit is "Unlimited" – Viscount

Answer 2

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").

Fantoccini

Right-click, save as, to CSV worked for me like a charm to get a huge text value out of an NTEXT field. – Sefton

Does not seem to work in SSMS 2012 any more, saving in CSV or TXT only saved the first 8002 characters in my case (VARCHAR(MAX) with some ~16Kb of text) – Ecclesiasticism

@ajeh: which version of SSMS 2012 are you using? I just tested it (followed the same exact steps I described in my answer) with my SSMS 2012 (version 11.0.5343.0) and it works – Fantoccini

But do not open it in Excel, the CSV import will truncate the data again. :) – Thoracotomy

Thanks, this allowed me to export 7MB of text data from a varchar(max) field using SSMS 2012. – Ramayana

Saving as tab delimited text works better. The csv format doubles all the quotes in the text. – Mannerism

Exporting as CSV ruins the JSON by adding double quotes. Exporting as TAB delimited ruins the JSON by inserting newlines after 2037 characters (apparently max line length). It does not seem possible to make SSMS to handover any JSON longer than approx 2000 characters. – Artificer

I had to save as TEXT. In my case, when saved to csv, it escaped double quotes with pairs of double quotes. – Bufordbug

Using SSMS 17.9.1, I was able to get up to 10MB of JSON data out, everything else is truncated. I used Save as type and All files (not CSV or Text) to prevent any unnecessary characters from being added. – Whaleboat

save to csv worked for me. I just opened the file with NotePad++ and copied the value – Marplot

Works good, except it fiddles with carriage returns/line feed characters (which are allowed by the CSV standard - section 2 item 6), and does not write them to the output .CSV file. So typically Microsoft - trying to outsmart it's paying customers, and doing a face-palm instead. – Scientism

Answer 3

Quick trick-

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

Sonyasoo

This works perfectly (without the CDATA part) when the contents of the column is actually XML – Cheliform

This worked for me as well, I had json data that I needed to get out of ms sql. – Ferry

Also, the contents are XML escaped. Once you take the text out, you should unescape it using a tool like freeformatter.com. – Ectype

This worked for me: ``` DECLARE @g geography; ... select Cast(@g.ToString() as xml); ``` – Leitmotif

This worked for me with SSMS v18 to view 250kb json string which was truncated by other solutions. Plus I like it because it gives you a link to a text viewer. +! – Zirconium

Or better: CAST('' AS xml).query('sql:column("Logs.LogInfo")') – Corticate

Answer 4

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):

Bedside

Looks like a really useful extension, but I cannot accept it as an answer as it requires a commercial product (or trial). Thanks though! – Catchpole

+1 Nice free extension that works perfectly... Thanks! – Revetment

I know links to software are considered as bad answers due to the potential of it disappearing, but this is a very quick to setup solution to a problem which has not been answered by changing settings alone. – Overturn

I use SSMS Boost all the time. It really is free, and I'd be much slower without it. – Scientism

Unfortunately, as of Sept 4, 2017, SSMS Boost is no longer free (except for a 30 day trial for each new version released) – Sketchy

SSMS Boost has a Free community edition. As of the date of this comment, they require you to download the latest version every 4 months. I can live with that... – Salutation

You have to buy the Pro version to use the "Visualizer" feature as depicted above. – Corruptible

As of 5/9/18, with version 3.4 of the plugin, I noticed it cut off text at 10,485,761 characters. The same happened with the "save-as" csv method as well. So not even this method works. – Lynnett

This is now a commercial product, the free license doesn't cover any of the mentioned features, they are disabled and you get a pop-up to buy the PRO version. The answer is just a promotion for his software. – Forthright

This answer should be deleted, useful but a waste of time just to realize it's paid. – Patnode

Users writing about "paid feature" and "product promotion": The original answer above was written in 2012. The add-in was under heavy development from 2011 till late 2017 and remained despite that 100% free all that time. At some stage to support it's continuity it was turned to paid product, leaving some of features free. I am happy to provide easy to use solution for those, who find features of SSMSBoost useful and value time they save using it. – Bedside

As of the posting of this comment, SSMS Boost's last release was in Apr 7, 2021, is not compatible with SSMS 19, and appears to no longer be in development. The Free Community Edition requires you download the latest version, but because there are no more releases, there's no latest version to download to refresh your license. – Viscount

New version with SSMS 19.x was released on 4-th March, 2024 – Bedside

Answer 5

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.

Dutiable

The esiest way so far – Trela

Answer 6

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')

Tout

Very simple and effective solution. – Donald

Answer 7

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" `
    -MaxCharLength 2147483647 -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.

Gloucestershire

Answer 8

Use the Visual Studio Code SQL Server Plugin. It is useful for JSON.

• Microsoft Documentation: SQL Server extension for Visual Studio Code
• Extension: SQL Server (mssql)

Boatel

Answer 9

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!

Ssw

Answer 10

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

Protolanguage

copy/paste truncates data. Only the “Save Results As…” option works correctly – Pentecostal

Answer 11

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

declare @t varchar(max)

select @t = long_column from table

print @t

Brat

But text is still truncated :-( – Nationalist

Yeaaah, still truncated :( – Tichon

instead of pirnt @t do select @t and you can copy the full text from SSMS output window into word. – Audry