How to Get the Full Resultset from Ssms

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

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.

View schema of resultset in SQL Server Management Studio

Quick and dirty snippet, requires all the fields in the result set are named or aliased;

select * into #T 
from 
  openrowset('SQLNCLI', 'Server=.;Trusted_Connection=yes;', 'exec thedb.dbo.sp_whatever')
exec('use tempdb exec sp_columns #T drop table #T')

How do I view the full content of a text or varchar(MAX) column in SQL Server 2008 Management Studio?

SSMS only allows unlimited data for XML data. This is not the default and needs to be set in the options.

One trick which might work in quite limited circumstances is simply naming the column in a special manner as below so it gets treated as XML data.

DECLARE @S varchar(max) = 'A'

SET @S =  REPLICATE(@S,100000) + 'B' 

SELECT @S as [XML_F52E2B61-18A1-11d1-B105-00805F49916B]

In SSMS (at least versions 2012 to current of 18.3) this displays the results as below

Clicking on it opens the full results in the XML viewer. Scrolling to the right shows the last character of B is preserved,

However this does have some significant problems. Adding extra columns to the query breaks the effect and extra rows all become concatenated with the first one. Finally if the string contains characters such as < opening the XML viewer fails with a parsing error.

A more robust way of doing this that avoids issues of SQL Server converting < to < etc or failing due to these characters is below (credit Adam Machanic here).

DECLARE @S varchar(max)

SELECT @S = ''

SELECT @S = @S + '
' + OBJECT_DEFINITION(OBJECT_ID) FROM SYS.PROCEDURES

SELECT @S AS [processing-instruction(x)] FOR XML PATH('')

Resultset column width in Management Studio

No, the width of each column is determined at runtime, and there is no way to override this in any version of Management Studio I've ever used. In fact I think the algorithm got worse in SQL Server 2008, and has been essentially the same ever since - you can run the same resultset twice, and the grid is inconsistent in the same output (this is SQL Server 2014 CTP2):

I reported this bug in 2008, and it was promptly closed as "Won't Fix":

• SSMS : Grid alignment, column width seems arbitrary (sorry, no link)

If you want control over this, you will either have to create an add-in for Management Studio that can manhandle the results grid, or you'll have to write your own query tool.

Update 2016-01-12: This grid misalignment issue should have been fixed in some build of Management Studio (well, the UserVoice item had been updated, but they admit it might still be imperfect, and I'm not seeing any evidence of a fix).

Update 2021-10-13: I updated this item in 2016 when Microsoft unplugged Connect and migrated some of the content to UserVoice. Now they have unplugged UserVoice as well, so I apologize the links above had to be removed, but this issue hasn't been fixed in the meantime anyway (just verified in SSMS 18.10).

How to search for a string/substring in SSMS results?

In SQL Server Management Studio, you can output your query results to text (CTRL + T), re-run your query, click in the results pane, and CTRL + F to find strings from an unfiltered query. To revert query results to grid, hit CTRL + D.

How to keep results from previous queries viewable in SSMS

SSMS doesn't have this feature. If you want to temporarily (or permanently) save the results of a query, open a new query (Ctrl + N) and run it in that separate window.

---

Related Topics