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 Statement 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?
You could do a WHILE
loop based on the count on your script length divided by 8000.
EG:
DECLARE @Counter INT
SET @Counter = 0
DECLARE @TotalPrints INT
SET @TotalPrints = (LEN(@script) / 8000) + 1
WHILE @Counter < @TotalPrints
BEGIN
-- Do your printing...
SET @Counter = @Counter + 1
END