如何打印 VARCHAR (MAX)使用打印语句？

我的准则是:

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)

脚本的长度大约是10,000个字符，因为我使用的 print 语句只能容纳最多8000个字符。所以我使用了两个 print 语句。

问题是，当我有一个18000个字符的脚本时，我过去常常使用3个 print 语句。

那么有没有一种方法可以根据脚本的长度来设置 print 语句的数量呢？

124281 次浏览
小开
最佳答案

您可以根据脚本长度除以8000的计数来执行 WHILE循环。

例如:

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
小开

下面的解决方案不使用 PRINT语句。它与 SQLServerManagementStudio 结合起来工作得很好。

SELECT CAST('<root><![CDATA[' + @MyLongString + ']]></root>' AS XML)

您可以单击返回的 XML 以在内置的 XML 查看器中展开它。

对于显示的大小有一个相当大的客户端限制。如果需要，转到 Tools/Options/Query Results/SQL Server/Results to Grid/XML data进行调整。

小开

我知道这是一个老问题，但我做了什么没有提到这里。

对我来说，下面的工作(高达16k 字符)

DECLARE @info NVARCHAR(MAX)


--SET @info to something big


PRINT CAST(@info AS NTEXT)

如果你有超过16k 个字符，你可以像这样结合@Yovav 的回答(64k 对任何人来说都足够了;)

    print cast( substring(@info, 1, 16000) as ntext )
print cast( substring(@info, 16001, 32000) as ntext )
print cast( substring(@info, 32001, 48000) as ntext )
print cast( substring(@info, 48001, 64000) as ntext )
小开

我们应该这样做:

DECLARE @String NVARCHAR(MAX);
DECLARE @CurrentEnd BIGINT; /* track the length of the next substring */
DECLARE @offset tinyint; /*tracks the amount of offset needed */
set @string = replace(  replace(@string, char(13) + char(10), char(10))   , char(13), char(10))


WHILE LEN(@String) > 1
BEGIN
IF CHARINDEX(CHAR(10), @String) between 1 AND 4000
BEGIN
SET @CurrentEnd =  CHARINDEX(char(10), @String) -1
set @offset = 2
END
ELSE
BEGIN
SET @CurrentEnd = 4000
set @offset = 1
END
PRINT SUBSTRING(@String, 1, @CurrentEnd)
set @string = SUBSTRING(@String, @CurrentEnd+@offset, LEN(@String))
END /*End While loop*/

取自 http://ask.sqlservercentral.com/questions/3102/any-way-around-the-print-limit-of-nvarcharmax-in-s.html

小开

我希望使用 print 语句来调试一些动态 sql，因为我想大多数人使用 print 也是出于类似的原因。

我尝试了列出的几个解决方案，发现 Kelsey 的解决方案只需要小 tweeks (@sql 是我的@script) n.b. LENGTH 不是一个有效的函数:

--http://stackoverflow.com/questions/7850477/how-to-print-varcharmax-using-print-statement
--Kelsey
DECLARE @Counter INT
SET @Counter = 0
DECLARE @TotalPrints INT
SET @TotalPrints = (LEN(@sql) / 4000) + 1
WHILE @Counter < @TotalPrints
BEGIN
PRINT SUBSTRING(@sql, @Counter * 4000, 4000)
SET @Counter = @Counter + 1
END
PRINT LEN(@sql)

这段代码会像注释一样在输出中添加一行新代码，但是对于调试来说，这不是问题。

Ben B 的解决方案是完美的，也是最优雅的，尽管调试需要很多行代码，所以我选择使用我对 Kelsey 的稍微修改。也许值得在 msdb 中为 Ben B 的代码创建一个类似存储过程的系统，它可以在一行中重用和调用？

不幸的是，Alfoks 的代码不起作用，因为那样会更容易。

小开

这是另一个版本。这个方法从主字符串中提取出要打印的每个子字符串，而不是在每个循环中将主字符串减少4000(这可能会在底层创建很多非常长的字符串——不确定)。

CREATE PROCEDURE [Internal].[LongPrint]
@msg nvarchar(max)
AS
BEGIN


-- SET NOCOUNT ON reduces network overhead
SET NOCOUNT ON;


DECLARE @MsgLen int;
DECLARE @CurrLineStartIdx int = 1;
DECLARE @CurrLineEndIdx int;
DECLARE @CurrLineLen int;
DECLARE @SkipCount int;


-- Normalise line end characters.
SET @msg = REPLACE(@msg, char(13) + char(10), char(10));
SET @msg = REPLACE(@msg, char(13), char(10));


-- Store length of the normalised string.
SET @MsgLen = LEN(@msg);


-- Special case: Empty string.
IF @MsgLen = 0
BEGIN
PRINT '';
RETURN;
END


-- Find the end of next substring to print.
SET @CurrLineEndIdx = CHARINDEX(CHAR(10), @msg);
IF @CurrLineEndIdx BETWEEN 1 AND 4000
BEGIN
SET @CurrLineEndIdx = @CurrLineEndIdx - 1
SET @SkipCount = 2;
END
ELSE
BEGIN
SET @CurrLineEndIdx = 4000;
SET @SkipCount = 1;
END


-- Loop: Print current substring, identify next substring (a do-while pattern is preferable but TSQL doesn't have one).
WHILE @CurrLineStartIdx < @MsgLen
BEGIN
-- Print substring.
PRINT SUBSTRING(@msg, @CurrLineStartIdx, (@CurrLineEndIdx - @CurrLineStartIdx)+1);


-- Move to start of next substring.
SET @CurrLineStartIdx = @CurrLineEndIdx + @SkipCount;


-- Find the end of next substring to print.
SET @CurrLineEndIdx = CHARINDEX(CHAR(10), @msg, @CurrLineStartIdx);
SET @CurrLineLen = @CurrLineEndIdx - @CurrLineStartIdx;


-- Find bounds of next substring to print.
IF @CurrLineLen BETWEEN 1 AND 4000
BEGIN
SET @CurrLineEndIdx = @CurrLineEndIdx - 1
SET @SkipCount = 2;
END
ELSE
BEGIN
SET @CurrLineEndIdx = @CurrLineStartIdx + 4000;
SET @SkipCount = 1;
END
END
END
小开

这个过程正确地打印出 VARCHAR(MAX)参数，考虑到包装:

CREATE PROCEDURE [dbo].[Print]
@sql varchar(max)
AS
BEGIN
declare
@n int,
@i int = 0,
@s int = 0, -- substring start posotion
@l int;     -- substring length


set @n = ceiling(len(@sql) / 8000.0);


while @i < @n
begin
set @l = 8000 - charindex(char(13), reverse(substring(@sql, @s, 8000)));
print substring(@sql, @s, @l);
set @i = @i + 1;
set @s = @s + @l + 2; -- accumulation + CR/LF
end


return 0
END
小开

这应该可以正常工作，这只是对以前的答案的一个改进。

DECLARE @Counter INT
DECLARE @Counter1 INT
SET @Counter = 0
SET @Counter1 = 0
DECLARE @TotalPrints INT
SET @TotalPrints = (LEN(@QUERY) / 4000) + 1
print @TotalPrints
WHILE @Counter < @TotalPrints
BEGIN
-- Do your printing...
print(substring(@query,@COUNTER1,@COUNTER1+4000))


set @COUNTER1 = @Counter1+4000
SET @Counter = @Counter + 1
END
小开 
create procedure dbo.PrintMax @text nvarchar(max)
as
begin
declare @i int, @newline nchar(2), @print varchar(max);
set @newline = nchar(13) + nchar(10);
select @i = charindex(@newline, @text);
while (@i > 0)
begin
select @print = substring(@text,0,@i);
while (len(@print) > 8000)
begin
print substring(@print,0,8000);
select @print = substring(@print,8000,len(@print));
end
print @print;
select @text = substring(@text,@i+2,len(@text));
select @i = charindex(@newline, @text);
end
print @text;
end
小开

遇到这个问题，想要一些更简单的... 试试以下方法:

SELECT [processing-instruction(x)]=@Script FOR XML PATH(''),TYPE
小开

你可以用这个

declare @i int = 1
while Exists(Select(Substring(@Script,@i,4000))) and (@i < LEN(@Script))
begin
print Substring(@Script,@i,4000)
set @i = @i+4000
end
小开

使用换行和空格作为一个很好的断点:

declare @sqlAll as nvarchar(max)
set @sqlAll = '-- Insert all your sql here'


print '@sqlAll - truncated over 4000'
print @sqlAll
print '   '
print '   '
print '   '


print '@sqlAll - split into chunks'
declare @i int = 1, @nextspace int = 0, @newline nchar(2)
set @newline = nchar(13) + nchar(10)




while Exists(Select(Substring(@sqlAll,@i,3000))) and (@i < LEN(@sqlAll))
begin
while Substring(@sqlAll,@i+3000+@nextspace,1) <> ' ' and Substring(@sqlAll,@i+3000+@nextspace,1) <> @newline
BEGIN
set @nextspace = @nextspace + 1
end
print Substring(@sqlAll,@i,3000+@nextspace)
set @i = @i+3000+@nextspace
set @nextspace = 0
end
print '   '
print '   '
print '   '
小开

有一个很棒的函数叫做 译自: 美国《科学》杂志网站(http://weblogs.asp.net/bdill/sql-server-print-max)原文地址: http://weblogs.asp.net/bdill/sql-server-print-max。

下面是稍作修改的版本，它使用临时存储过程来避免“模式污染”(来自 https://github.com/Toolien/sp_GenMerge/blob/master/sp_GenMerge.sql的想法)

EXEC (N'IF EXISTS (SELECT * FROM tempdb.sys.objects
WHERE object_id = OBJECT_ID(N''tempdb..#PrintMax'')
AND type in (N''P'', N''PC''))
DROP PROCEDURE #PrintMax;');
EXEC (N'CREATE PROCEDURE #PrintMax(@iInput NVARCHAR(MAX))
AS
BEGIN
IF @iInput IS NULL
RETURN;


DECLARE @ReversedData NVARCHAR(MAX)
, @LineBreakIndex INT
, @SearchLength INT;


SET @SearchLength = 4000;


WHILE LEN(@iInput) > @SearchLength
BEGIN
SET @ReversedData = LEFT(@iInput COLLATE DATABASE_DEFAULT, @SearchLength);
SET @ReversedData = REVERSE(@ReversedData COLLATE DATABASE_DEFAULT);
SET @LineBreakIndex = CHARINDEX(CHAR(10) + CHAR(13),
@ReversedData COLLATE DATABASE_DEFAULT);
PRINT LEFT(@iInput, @SearchLength - @LineBreakIndex + 1);
SET @iInput = RIGHT(@iInput, LEN(@iInput) - @SearchLength
+ @LineBreakIndex - 1);
END;


IF LEN(@iInput) > 0
PRINT @iInput;
END;');

DBFiddle 演示

编辑:

使用 CREATE OR ALTER我们可以避免两个 EXEC 调用:

EXEC (N'CREATE OR ALTER PROCEDURE #PrintMax(@iInput NVARCHAR(MAX))
AS
BEGIN
IF @iInput IS NULL
RETURN;


DECLARE @ReversedData NVARCHAR(MAX)
, @LineBreakIndex INT
, @SearchLength INT;


SET @SearchLength = 4000;


WHILE LEN(@iInput) > @SearchLength
BEGIN
SET @ReversedData = LEFT(@iInput COLLATE DATABASE_DEFAULT, @SearchLength);
SET @ReversedData = REVERSE(@ReversedData COLLATE DATABASE_DEFAULT);
SET @LineBreakIndex = CHARINDEX(CHAR(10) + CHAR(13), @ReversedData COLLATE DATABASE_DEFAULT);
PRINT LEFT(@iInput, @SearchLength - @LineBreakIndex + 1);
SET @iInput = RIGHT(@iInput, LEN(@iInput) - @SearchLength + @LineBreakIndex - 1);
END;


IF LEN(@iInput) > 0
PRINT @iInput;
END;');

分贝 < > 小提琴演奏

小开

如果源代码将不会有问题与 LF 被 CRLF 所取代，没有调试是必需的以下简单的代码输出。

--http://stackoverflow.com/questions/7850477/how-to-print-varcharmax-using-print-statement
--Bill Bai
SET @SQL=replace(@SQL,char(10),char(13)+char(10))
SET @SQL=replace(@SQL,char(13)+char(13)+char(10),char(13)+char(10) )
DECLARE @Position int
WHILE Len(@SQL)>0
BEGIN
SET @Position=charindex(char(10),@SQL)
PRINT left(@SQL,@Position-2)
SET @SQL=substring(@SQL,@Position+1,len(@SQL))
end;
小开

或者简单地说:

PRINT SUBSTRING(@SQL_InsertQuery, 1, 8000)
PRINT SUBSTRING(@SQL_InsertQuery, 8001, 16000)
小开

我刚刚创建了一个 SP 的 本的伟大的 回答:

/*
---------------------------------------------------------------------------------
PURPOSE   : Print a string without the limitation of 4000 or 8000 characters.
https://stackoverflow.com/questions/7850477/how-to-print-varcharmax-using-print-statement
USAGE     :
DECLARE @Result NVARCHAR(MAX)
SET @Result = 'TEST'
EXEC [dbo].[Print_Unlimited] @Result
---------------------------------------------------------------------------------
*/
ALTER PROCEDURE [dbo].[Print_Unlimited]
@String NVARCHAR(MAX)
AS


BEGIN


BEGIN TRY
---------------------------------------------------------------------------------


DECLARE @CurrentEnd BIGINT; /* track the length of the next substring */
DECLARE @Offset TINYINT; /* tracks the amount of offset needed */
SET @String = replace(replace(@String, CHAR(13) + CHAR(10), CHAR(10)), CHAR(13), CHAR(10))


WHILE LEN(@String) > 1
BEGIN
IF CHARINDEX(CHAR(10), @String) BETWEEN 1 AND 4000
BEGIN
SET @CurrentEnd =  CHARINDEX(CHAR(10), @String) -1
SET @Offset = 2
END
ELSE
BEGIN
SET @CurrentEnd = 4000
SET @Offset = 1
END
PRINT SUBSTRING(@String, 1, @CurrentEnd)
SET @String = SUBSTRING(@String, @CurrentEnd + @Offset, LEN(@String))
END /*End While loop*/


---------------------------------------------------------------------------------
END TRY
BEGIN CATCH
DECLARE @ErrorMessage VARCHAR(4000)
SELECT @ErrorMessage = ERROR_MESSAGE()
RAISERROR(@ErrorMessage,16,1)
END CATCH
END
小开

我的 PrintMax 版本用于防止输出时出现坏行断裂:


CREATE PROCEDURE [dbo].[PrintMax](@iInput NVARCHAR(MAX))
AS
BEGIN
Declare @i int;
Declare @NEWLINE char(1) = CHAR(13) + CHAR(10);
While LEN(@iInput)>0 BEGIN
Set @i = CHARINDEX(@NEWLINE, @iInput)
if @i>8000 OR @i=0 Set @i=8000
Print SUBSTRING(@iInput, 0, @i)
Set @iInput = SUBSTRING(@iInput, @i+1, LEN(@iInput))
END
END
小开

如果有人对此感兴趣，我最终会用 powershell 生成一个文本文件，执行标量代码:

$dbconn = "Data Source=sqlserver;" + "Initial Catalog=DatabaseName;" + "User Id=sa;Password=pass;"
$conn = New-Object System.Data.SqlClient.SqlConnection($dbconn)
$conn.Open()


$cmd = New-Object System.Data.SqlClient.SqlCommand("
set nocount on


DECLARE @sql nvarchar(max) = ''


SELECT
@sql += CHAR(13) + CHAR(10) + md.definition + CHAR(13) + CHAR(10) + 'GO'
FROM sys.objects AS obj
join sys.sql_modules AS md on md.object_id = obj.object_id
join sys.schemas AS sch on sch.schema_id = obj.schema_id
where obj.type = 'TR'




select @sql
", $conn)
$data = [string]$cmd.ExecuteScalar()




$conn.Close()


$data | Out-File -FilePath "C:\Users\Alexandru\Desktop\bigstring.txt"

这个脚本用于从 DB 获取包含所有触发器的大字符串。


提交答案