Search code examples
sql-serversql-server-2008t-sqlssms

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.

Solution

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

    enter image description here

    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.