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

User · Accepted Answer

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

User · Answer

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

User · Answer

The easiest way to quickly view large varchar text column    declare  t varchar max   select  t   long column from table  print  t

User · Answer

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  lt ROOT gt  lt  ROOT gt  element  The contents of the individual rows will each be contained within an element that  for this example  is called  lt COL NVARCHAR MAX gt   The name of that can be changed using an alias via AS  Special characters like  amp    lt  or  gt  or similar will be converted to their respective entities  So you may have to convert  amp lt    amp gt  and  amp amp  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

User · Answer

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

User · Answer

Quick trick-  SELECT CAST   lt A gt  lt   CDATA     CAST LogInfo as nvarchar max         gt  lt  A gt   AS xml  FROM Logs WHERE IDLog   904862629

User · Answer

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  amp  instead of  amp amp  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

User · Answer

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

User · Answer

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

User · Answer

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  quot select BigColumn from SomeTable where Id   123 quot        -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

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

Examples related to sql-server

Examples related to sql-server-2008

Examples related to tsql

Examples related to ssms