如何使用Print语句打印Varchar（Max）？

Question

我有一个代码：

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个字符，并且由于我使用的是只能容纳最大8000的打印语句。因此，我使用两个打印语句。

问题是当我有一个脚本为18000个字符的脚本时，我曾经使用3个打印语句。

因此，有一种方法可以根据脚本的长度设置打印语句的数量吗？

Answer 1

如果有人感兴趣的话，我最终是用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。

Answer 2

我阅读了上面的内容，并尝试了自己的简化版本，该版本以正确的垂直间距输出过程文本。
可变声明

DECLARE @tp     as varchar(100)
DECLARE @def    as nvarchar(MAX)
DECLARE @prt1   as nvarchar(4000)
DECLARE @prt2   as nvarchar(MAX)
DECLARE @pos1   as int
DECLARE @pos2   as int

在我的实现中，我希望输出以前查询中列出并保存在临时表中的过程列表。然后，我将它们设置为要输出的过程。我将变量@DEF设置为过程定义

    SET @def = (SELECT  CAST(m.definition as nvarchar(MAX))
                FROM    sys.objects o
                            LEFT JOIN sys.sql_modules m
                            on m.object_id = o.object_id
                WHERE   o.name = @tp) 

，然后我使用一个简单的“雕刻循环”来缩小输出的大小，以与SSMS消息输出窗口兼容，

    WHILE (Len(@def) > 4000)
      BEGIN
        SET @pos1 = CHARINDEX(CHAR(13), @Def,3600)
        SET @prt1 = SUBSTRING(@def,1,@pos1-1)
        SET @pos2 = LEN(@Def)
        SET @prt2 = SUBSTRING(@def, @pos1+2, @pos2-@pos1)
        SET @def = @prt2
        print @prt1 
      END
    Print @def

一旦它低于4K字符限制，我简单地输出了什么。我们绕过了通过打印命令提供的中间CR/LF。这没有什么革命性的，但是有直接且易于阅读的好处。

Answer 3

我知道这是一个古老的问题，但是我在这里没有提及。

对我来说，以下工作（最多可容纳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, 16000) as ntext )
    print cast( substring(@info, 32001, 16000) as ntext )
    print cast( substring(@info, 48001, 16000) as ntext )

Answer 4

以下解决方法不使用 print 语句。与SQL Server Management Studio结合使用，它可以很好地工作。

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

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

显示的大小有一个相当大的客户端限制。转到工具/选项/查询结果/SQL Server/结果到Grid/XML数据在需要时进行调整。

Answer 5

这是应该做到的：

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-the-print-limit-o-nvarcharmax-in-s.html

Answer 6

您可以根据脚本长度的计数除以8000

。

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

Answer 7

遇到了这个问题，想要一些简单的东西...尝试以下内容：

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

Answer 8

我刚刚从答案：

/*
---------------------------------------------------------------------------------
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

Answer 9

此PROC正确打印出 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

Answer 10

我想使用打印语句来调试一些动态的SQL，因为我想象大多数人都出于Simliar原因而使用Print。

我尝试了一些列出的解决方案，发现Kelsey的解决方案可与次要镊子（@sql是我的@Script）一起使用NB长度不是一个有效的功能：

--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的稍作修改。对于Ben B的代码中，可以在MSDB中创建一个可以重复使用并列为一行的系统？

不幸的是，Alfoks的代码无法正常工作，因为这会更容易。

Answer 11

或简单：

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

Answer 12

你可以使用这个

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

Answer 13

有很棒的功能称为 bennett dill /strong>。

这是稍微修改的版本，它使用临时存储过程来避免“模式polution”（ 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 demo

编辑：

使用创建或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;');

Answer 14

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

Answer 15

使用线条提要和空间作为一个很好的断点：

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 '   '

Answer 16

我的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

Answer 17

这是另一个版本。这是从主字符串中提取每个子字符串，而不是在每个循环上将主字符串减少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

Answer 18

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

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

Answer 19

如果源代码将没有CRLF替换的LF问题，则通过以下简单的代码输出不需要调试。

--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;