在 SQL Server 中打印出文本字段内容的最简单方法

Question

我需要使用 MS 查询分析器输出文本字段的内容。我试过这个：

select top 1 text from myTable

（其中文本是text字段）

和

DECLARE @data VarChar(8000) 
select top 1 @data = text from myTable
PRINT @data

第一个仅打印前 2000 个字符

左右，第二个仅打印前 8000 个字符。有没有办法获取所有文本？

笔记：

• 必须与 SQL Server 7 一起使用

Answer 1

我不认为你可以在MSSQL7中使用varchar（MAX），所以这里有一些东西可以给你所有的数据（注意，我的理解是你只是想直观地看到数据，你不会把它放在变量中或返回它）。

因此，这将打印出整个字符串，以便您可以直观地查看字段中的内容：

DECLARE @limit as int,
        @charLen as int,
        @current as int,
        @chars as varchar(8000)
SET @limit = 8000
SELECT  TOP 1 @charLen = LEN(text)
FROM    myTable
SET @current = 1
WHILE @current < @charLen
BEGIN
    SELECT  TOP 1 @chars = SUBSTRING(text,@current,@limit)
    FROM    myTable
    PRINT @chars
    SET @current = @current + @limit
END

Answer 2

我已经有

一段时间没有使用查询分析器了，但是您可以调整"选项"窗口的结果窗口中显示的最大字符数。 请参阅 MSDN 文档。

Answer 3

http://shortfastcode.blogspot.com/2011/10/getting-around-sql-server-print-8000.html

使用这个存储的过程。 唯一的缺点是每 8000 个字符:(

CREATE PROCEDURE [dbo].[LongPrint]
      @String NVARCHAR(MAX)
AS
/*
Example:
exec LongPrint @string =
'This String
Exists to test
the system.'
*/
/* This procedure is designed to overcome the limitation
in the SQL print command that causes it to truncate strings
longer than 8000 characters (4000 for nvarchar).
It will print the text passed to it in substrings smaller than 4000
characters.  If there are carriage returns (CRs) or new lines (NLs in the text),
it will break up the substrings at the carriage returns and the
printed version will exactly reflect the string passed.
If there are insufficient line breaks in the text, it will
print it out in blocks of 4000 characters with an extra carriage
return at that point.
If it is passed a null value, it will do virtually nothing.
NOTE: This is substantially slower than a simple print, so should only be used
when actually needed.
 */
DECLARE
               @CurrentEnd BIGINT, /* track the length of the next substring */
               @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, 1073741822)
END /*End While loop*/

这最初发表在 SQLServerCentral.com at http://www.sqlservercentral.com/scripts/Print/63240/