Sql-server – Get complete value of varchar(max) field – SQL Server 2005

sql-server-2005varchar

I have searched for a couple of hours but cannot find the exact solution for this.

In my SQL Server 2005 database table (tableA) there is a varchar(max) column.

I want to get complete value of that column (now it is not displaying any value) in SQL Server Management Studio.

Can anybody provide me a solution?

Best Answer

If the issue you are having is Truncation you can use this XML approach that gets around most of the issues with XML entitisation.

declare @VeryLongText nvarchar(max) = '';

SELECT top 100 @VeryLongText = @VeryLongText + '

' + OBJECT_DEFINITION(object_id) 
FROM sys.all_objects 
WHERE type='P' and is_ms_shipped=1

SELECT LEN(@VeryLongText)

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

PRINT @VeryLongText /*WILL be truncated*/

Make sure that the "XML data" limit in SSMS is set sufficiently high!

Screenshot

Related Question