How to print VARCHAR(MAX) using Print Statement?

Question

I have a code which is:

DECLARE @Script VARCHAR(MAX)

SELECT @Script = definition FROM manged.sys.all_sql_modules sq

where sq.object_id = (SELECT object_id from managed.sys.objects 

Where type = 'P' and Name = 'usp_gen_data')

Declare @Pos int

SELECT  @pos=CHARINDEX(CHAR(13)+CHAR(10),@script,7500)

PRINT SUBSTRING(@Script,1,@Pos)

PRINT SUBSTRING(@script,@pos,8000)

The length of the Script is around 10,000 Characters and Since I am using print Statements which can hold only max of 8000. So I am using two print statements.The problem is when I have a script which is of say 18000 characters then I used to use 3 print statements.

So Is there a way that I could set the number of print statements depending on the length of the script?

Answer 1

You can try using the code given below:

DECLARE @info NVARCHAR(MAX)

--SET @info to something big

PRINT CAST(@info AS NTEXT)