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

by

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

enter image description here

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

enter image description here

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 &lt; 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('')

More Related Contents:

  1. How do you view ALL text from an ntext or nvarchar(max) in SSMS?
  2. How can I automate the “generate scripts” task in SQL Server Management Studio 2008?
  3. Query times out when executed from web, but super-fast when executed from SSMS
  4. Unique constraint on multiple columns
  5. The request failed or the service did not respond in a timely fashion?
  6. If condition in SQL Server 2008
  7. Comma separated results in SQL
  8. sql server 2008 management studio not checking the syntax of my query
  9. Understanding PIVOT function in T-SQL
  10. Which are more performant, CTE or temporary tables?
  11. Using RegEx in SQL Server
  12. What is the best way to auto-generate INSERT statements for a SQL Server table?
  13. Import Excel spreadsheet columns into SQL Server database
  14. INSERT INTO vs SELECT INTO
  15. SQL Server 2008: how do I grant privileges to a username?
  16. How do I use the CONCAT function in SQL Server 2008 R2?
  17. SQL Server sp_msforeachtable usage to select only those tables which meet some condition
  18. Is it possible to force row level locking in SQL Server?
  19. Select / Insert version of an Upsert: is there a design pattern for high concurrency?
  20. How do I get date/time information from a TIMESTAMP column?
  21. How to add xml encoding to xml Output in SQL Server
  22. What happens to the primary key Id when it goes over the limit?
  23. SQL Recursive CTE: Finding objects linked by property
  24. Convert decimal time to hours and minutes
  25. Sort Bullets in Database
  26. Converting Select results into Insert script – SQL Server
  27. FOR JSON PATH results in SSMS truncated to 2033 characters
  28. SQL Server 2008 can’t login with newly created user
  29. Find broken objects in SQL Server
  30. Populate Dataset With Table Names From Stored Procedure

Leave a Comment