在SQL Server 2008管理工具中，如何查看文本或varchar(MAX)列的完整内容？

Question

在这个使用 SQL Server 2008（版本号为10.0.1600）的数据库中，有一个名为“Events”的表，其中包含一个名为“Details”的“text”列。（是的，我意识到它实际上应该是一个varchar(MAX)列，但是设置此数据库的人没有这样做。）

这个列包含非常大的异常日志和相关的JSON数据，我正在尝试通过SQL Server Management Studio访问它们，但每当我将网格中的结果复制到文本编辑器时，它都会在43679个字符处截断。

我曾经在互联网的不同位置上阅读过，您可以将查询结果的XML数据的最大检索字符数在“工具 > 选项 > 查询结果 > SQL Server > 结果到网格”中设置为无限制，然后执行类似于以下的查询：

select Convert(xml, Details) from Events
where EventID = 13920

（请注意，数据列根本不是XML。将该列转换为XML仅是我通过Google找到的解决方法，别人曾用它来避免从text或varchar（MAX）列检索数据时SSMS的限制。）

然而，在设置了上述选项后，运行查询并单击结果中的链接，我仍然会收到以下错误消息：

无法显示XML。 发生了以下错误：已发生意外的文件结尾。 行5，位置220160。

其中一个解决方案是增加从服务器检索XML数据的字符数。 要更改此设置，请在“工具”菜单上单击“选项”。

那么，有没有办法访问这些数据？将列转换为varchar（MAX）是否可以解决我的问题？

- adamjford

Answer 1

SSMS只允许XML数据无限制。这不是默认设置，需要在选项中进行设置。

一个可能仅适用于非常有限情况的技巧是以特殊方式命名列，使其被视为XML数据。

DECLARE @S varchar(max) = 'A'

SET @S =  REPLICATE(@S,100000) + 'B' 

SELECT @S as [XML_F52E2B61-18A1-11d1-B105-00805F49916B]

在 SSMS 中（至少在2012版本到当前的18.3版本），它会显示如下结果：

点击它会在 XML 查看器中打开完整的结果。向右滚动可以看到 B 的最后一个字符被保留了。

然而，这种方法存在一些重大问题。向查询添加额外列会破坏效果，额外行都会与第一行连接起来。最后，如果字符串包含诸如 < 这样的字符，则打开 XML 查看器时会出现解析错误。

以下是一种更健壮的方法，它避免了 SQL Server 将 < 转换为 < 等或由于这些字符而失败的问题（Adam Machanic 在这里获得信用）。

DECLARE @S varchar(max)

SELECT @S = ''

SELECT @S = @S + '
' + OBJECT_DEFINITION(OBJECT_ID) FROM SYS.PROCEDURES

SELECT @S AS [processing-instruction(x)] FOR XML PATH('')

- Martin Smith

---

将CDATA添加到Convert语句中确实起作用了！非常感谢。 :) - adamjford

添加 CDATA 可能有效，但是如果您的数据包含控制字符，则必须执行替换操作。在我的情况下，我在数据中使用了单元分隔符 ASCII 代码 31。由于我在许多地方仅使用了该字符，因此简单的 REPLACE(details, char(31), '&x1f;') 就足够了。如果我有未知字符或大量不同字符要替换，我可能需要找到另一种解决方案。 - Zarepheth

@Zarepheth - CDATA 是我的第一个建议。后面的 AS [processing-instruction(x)] 可以避免这个问题。 - Martin Smith

@MartinSmith 很棒的回答！想知道我从你的回答中学到了多少！+1 - Kin Shah

SSMS的设置生效了！我确实需要重新启动SSMS（18.9.2）才能使更改生效。 - Jan_V

Answer 2

我能让这个工作起来...

SELECT CAST('<![CDATA[' + LargeTextColumn + ']]>' AS XML) FROM TableName;

- Scott Durbin

Answer 3

一种解决方法是右键单击结果集，然后选择“另存为...”将其导出为 CSV 文件，该文件包含整个列的内容。虽然不完美，但对我来说效果不错。

- Oleg Fridman

---

除非上述配置得到解释，否则此操作不会导出完整内容。 - alegria

Answer 4

您尝试过这个简单的解决方案吗？只需要2次点击！

在查询窗口中，

1. 将查询选项设置为“结果到网格”，运行查询
2. 右键单击网格角落的结果选项卡，将结果保存为任何文件

您将在文件中获得所需查看的所有文本！！！对于我的varchar（MAX）字段的结果，我可以看到130556个字符。

- Jenna Leaf

---

直截了当，易于理解，但往往被遗忘，直到你再次需要它 :) - Dimi Takis

Answer 5

我找到的最简单的解决方法是备份表格并查看脚本。操作如下：

1. 右键单击您的数据库，选择 任务 > 生成脚本...
2. 在“介绍”页面上，点击 下一步
3. 在“选择对象”页面上
   1. 选择 选择特定的数据库对象 并选择您的表格。
   2. 点击 下一步
4. 在“设置脚本选项”页面上
   1. 将输出类型设置为 将脚本保存到特定位置
   2. 选择 保存到文件 并填写相关选项
   3. 点击 高级 按钮
   4. 将 常规 > 要编写脚本的数据类型 设置为 仅数据 或 架构和数据，然后点击确定
   5. 点击 下一步
5. 在“摘要页面”上点击下一步
6. 根据您在 4.2 中设置的选项，应该已经生成了您的 SQL 脚本。打开此文件并查看您的数据。

- Kevin Brydon

Answer 6

看起来Xml格式可能不正确。如果是这种情况，您将无法将其转换为Xml，因此在Management Studio中返回的文本量受到限制。但是，您可以将文本分成较小的块，例如：

With Tally As
    (
        Select ROW_NUMBER() OVER ( ORDER BY s1.object_id ) - 1 As Num
        From sys.sysobjects As s1
            Cross Join sys.sysobjects As s2
    )
Select Substring(T1.textCol, T2.Num * 8000 + 1, 8000)
From Table As T1
    Cross Join Tally As T2
Where T2.Num <= Ceiling(Len(T1.textCol) / 8000)
Order By T2.Num

然后您需要手动将它们再次组合。

编辑

听起来在 text 数据中有一些字符，Xml解析器无法识别。您可以尝试将这些值转换为实体，然后再尝试使用 Convert(xml, data) 技巧。因此，像这样：

Update Table
Set Data = Replace(Cast(Data As varchar(max)),'<','&lt;')

我需要将它转换为 varchar(max)，因为 replace 函数无法在 text 列上工作。你可以将这些 text 列转换为 varchar(max) 列，不应该有什么问题。

- Thomas

---

哦，我想我应该提一下，这列根本不是 XML。将该列转换为 XML 只是一个解决办法，我从谷歌上搜索到的，有人用它来避免 SSMS 从“text”或“varchar(MAX)”列中检索数据的限制。 - adamjford

编辑上面的代码;使用Tally作为 ( 选择ROW_NUMBER() OVER ( ORDER BY s1.id ) - 1 As Num 从sys.sysobjects As s1 Cross Join sys.sysobjects As s2 ) 选择子字符串(T1.YourTextCol, T2.Num * 8000 + 1, 8000) 从YourTable As T1 Cross Join Tally As T2 其中T2.Num <= Ceiling(datalength(T1.YourTextCol) / 8000) 按T2.Num排序 - Ian Quigley

使用@IanQuigley的TSQL，基于这个答案对我很有效。我基本上把结果（最终有11条记录）粘贴到记事本中并将它们拼接在一起。完美地工作了。需要保存这个脚本。我有一些包含无效字符的疯狂长的XML。 - atconway

Answer 7

数据类型TEXT已经过时，不应再使用，从TEXT列中选择数据很麻烦。 ntext、text和image（Transact-SQL）

在Microsoft SQL Server的将来版本中，ntext、text和image数据类型将被删除。避免在新开发工作中使用这些数据类型，并计划修改当前使用它们的应用程序。改用nvarchar(max)、varchar(max)和varbinary(max)。

您需要使用 TEXTPTR (Transact-SQL)检索文本数据。

此外，请参阅处理文本数据类型的文章。

- KM.

---

将列转换为 varchar(MAX) 是否可以防止 SSMS 截断其中的数据？ - adamjford

我会首先使用SSMS生成脚本将您的TEXT列转换为varchar(max)。从那里开始，您可以评估是否要进一步将其转换为XML列。至于SSMS显示限制，结果网格可以设置为每列显示最多65,535个非xml数据字符，以及无限量的xml数据（请注意，XML类型的表列只能存储2 GB）。而结果文本则限制每列8192个字符。要设置这些默认的最大限制，只需转到（在SSMS中）TOOLS菜单，然后选择OPTIONS...，然后选择QUERY RESULTS，然后选择SQL SERVER，然后选择RESULTS TO GRID或RESULTS TO TEXT。 - KM.

Answer 8

我想你运气不太好。问题并不是像其他答案所关注的那样在SQL层面上，而只是用户界面的问题。管理工具不是通用的数据访问接口，它不是你的接口，而是你的管理区域，并且在处理二进制数据和大量文本数据方面存在严重限制 - 因为使用指定的使用配置文件的人不会遇到这个问题。

简单地说，呈现大量文本数据并不是计划中的使用方式。

你唯一的选择就是创建一个表值函数，将文本输入分割成每行一行的行，这样管理工具就可以得到一系列的行，而不是单独的一行。

- TomTom

---

我觉得这个答案最多只是猜测，你有没有关于“计划使用”的任何来源？如果它是一个SQL“管理工具”，那么它至少应该能够显示我的数据库内容。界面设计非常糟糕。 - damd

此答案应被删除，因为它已不再适用。 - Caius Jard

Answer 9

我更喜欢这种简单的XML技巧，可以使SSMS中的列以单元格为单位可点击。使用此方法，您可以在SSMS的表格视图中快速查看数据，并单击特定单元格以查看它们有趣时的完整值。这与OP的技术相同，只是避免了XML错误。

SELECT
     e.EventID
    ,CAST(REPLACE(REPLACE(e.Details, '&', '&amp;'), '<', '&lt;') AS XML) Details
FROM Events e
WHERE 1=1
AND e.EventID BETWEEN 13920 AND 13930
;

- binki

---

请注意，某些字符在 XML 1.0 中无效，但在 NVARCHAR/VARCHAR 中有效。例如，NUL 字符（与字段值为 NULL 不同）。对于包含此类嵌入式值的字符串，转换将失败。 - binki

Answer 10

从SSMS 18.2开始，您现在可以在网格结果中查看高达200万个字符。 源

允许显示更多数据（结果转为文本）并将其存储在单元格中（结果转为网格）。 SSMS现在允许两者都有最多2M个字符。

我使用下面的代码进行了验证。

DECLARE @S varchar(max) = 'A'

SET @S =  REPLICATE(@S,2000000) + 'B' 

SELECT @S as a

- Gabe

---

最大字符数实际上是2097152（在SSMS版本15.0.18333.0中）。 - stomy