如何在SSMS中查看ntext或nvarchar（max）中的所有文本？

Question

如何在SQL Server Management Studio中查看NTEXT或NVARCHAR（max）中的所有文本？默认情况下，它似乎仅返回前几百个字符（255个字符？），但有时我只想快速查看整个字段，而无需编写程序来实现它。即使在SSMS 2012中仍存在此问题:(

- NickG

---

可能是如何从SSMS获取完整结果集的重复问题。 - Martin Smith

Answer 1

我可以通过选择（结果以网格形式）相应的列，并右键单击保存为CSV文件，从NVARCHAR（MAX）列中获取完整文本（99,208字符）。要查看结果，请使用文本编辑器（而不是Excel）打开CSV文件。有趣的是，当我尝试运行相同的查询，但启用了“结果保存到文件”，输出会使用结果保存到文本限制进行截断。

@MartinSmith针对当前被接受的答案所描述的解决方法在我的情况下无效（尝试查看完整XML结果时出错，抱怨“'['字符，十六进制值0x5B，在名称中无法包含”）。

- Eric

---

右键点击，选择“另存为”，将CSV文件格式保存，可以轻松地从NTEXT字段中提取出大量文本值。 - David Alpert

你使用的是哪个版本的SSMS 2012？我刚刚测试了一下（按照我回答中描述的完全相同的步骤），并且在我的SSMS 2012（版本11.0.5343.0）上成功运行。 - Eric

但不要在Excel中打开它，CSV导入会再次截断数据。 :) - Johncl

使用制表符分隔的文本保存方式效果更好。CSV格式会将文本中的所有引号都加倍。 - davidthegrey

将数据导出为CSV格式会破坏JSON格式，因为它会添加双引号。将数据导出为TAB分隔符也会破坏JSON格式，因为它会在2037个字符后插入换行符（显然是最大行长度）。似乎无法让SSMS处理超过约2000个字符的任何JSON数据。 - galmok

Answer 2

快速技巧 -

SELECT CAST('<A><![CDATA[' + CAST(LogInfo as nvarchar(max)) + ']]></A>' AS xml)
FROM Logs
WHERE IDLog = 904862629

- André Kops

---

如果列的内容实际上是XML，那么这段代码在没有CDATA部分的情况下可以完美运行。 - axk

这对我也起作用了，我有一些JSON数据需要从MS SQL中获取出来。 - Dev_Corps

另外，这些内容已经进行了XML转义。一旦您取出文本，应使用类似freeformatter.com的工具对其进行反转义。 - harsimranb

这对我有用：DECLARE @g geography; ... select Cast(@g.ToString() as xml); - Sergei Zinovyev

这对我来说很有效，使用SSMS v18查看250kb的JSON字符串，而其他解决方案则被截断。此外，我喜欢它，因为它提供了一个文本查看器的链接。+！ - Yogi

或者更好的方法：CAST('' AS xml).query('sql:column("Logs.LogInfo")') - Charlieface

Answer 3

在新版本的SSMS中，可以在“查询/查询选项/结果/网格/最大检索字符数”菜单中进行配置：

---

旧版的SSMS：

选项（查询结果/SQL Server/网格页的结果）

要更改当前查询的选项，请单击“查询”菜单上的“查询选项”，或在SQL Server查询窗口中单击鼠标右键并选择“查询选项”。

...

最大检索字符数
输入1到65535之间的数以指定每个单元格中显示的最大字符数。

你会发现最大值是64k。 默认值要小得多。

顺便说一下，结果为文本也有更严重的限制：

每列中显示的最大字符数
此值默认为256。 增加此值以显示更大的结果集而不截断。 最大值为8,192。

- Remus Rusanu

---

正如Remus在他的回答中写到的那样，由于使用MAX定义的列中数据类型的最大存储大小为2 GB，因此存在一些限制，因为在SSMS中显示所有的数据将是很繁琐的。 - Ivan Golović

并非如此。它可以像 XML 一样使用超链接，点击后会在新窗口中打开。我通常使用的解决方法是将其转换为 XML 如此。XML 数据可以设置为允许无限长度。 - Martin Smith

仅适用于字符数 < 65535，这远远少于ntext的最大值。 - Polyfun

@IvanG 即使“结果输出到文件”也受到此限制的影响。 - Iain

最新版本的SSMS限制更高。好的解决方案！ - FDM

Answer 4

我已经为 SSMS 编写了一个插件来解决这个问题。你可以使用以下两种方法之一：

第一种，你可以使用“复制当前单元格 1:1”将原始单元格数据复制到剪贴板中：

http://www.ssmsboost.com/Features/ssms-add-in-copy-results-grid-cell-contents-line-with-breaks

或者，你也可以使用“单元格可视化器”功能在外部文本编辑器（如 notepad++ 或 notepad）中打开单元格内容：http://www.ssmsboost.com/Features/ssms-add-in-results-grid-visualizers

(该功能允许在任何外部应用程序中打开字段内容，因此如果你知道它是文本，你可以使用文本编辑器打开它。如果内容是带有图片的二进制数据，则选择将其视为图片。下面的示例显示了打开图片的过程):

- Andrei Rantsevich

---

看起来这是一个非常实用的扩展程序，但我不能将其接受为答案，因为它需要商业产品（或试用版）。不过还是谢谢你！ - NickG

+1 很棒的免费扩展，完美地运作...谢谢！ - reSPAWNed

谢谢，这正是我想要的。 - Derreck Dean

很遗憾，自2017年9月4日起，SSMS Boost不再免费（除了每个新版本都有30天的试用期）。 - csrowell

现在这是一个商业产品，免费许可证不包含任何提到的功能，它们被禁用并弹出窗口提示您购买PRO版本。回答只是对他软件的推广。 - HamsterWithPitchfork

Answer 5

将数据作为XML返回

SELECT CONVERT(XML, [Data]) AS [Value]
FROM [dbo].[FormData]
WHERE [UID] LIKE '{my-uid}'

确保在SSMS选项窗口中设置一个合理的限制，这取决于你希望得到的结果。

如果要返回的文本包含未编码字符（例如使用&代替&），将导致XML转换失败。

使用PowerShell返回数据

为此，你需要在运行命令的计算机上安装PowerShell SQL Server模块。

如果已准备好，请配置并运行以下脚本：

Invoke-Sqlcmd -Query "SELECT [Data] FROM [dbo].[FormData] WHERE [UID] LIKE '{my-uid}'" -ServerInstance "database-server-name" -Database "database-name" -Username "user" -Password "password" -MaxCharLength 10000000 | Out-File -filePath "C:\db_data.txt"

请确保将-MaxCharLength参数设置为适合您需求的值。

- dvlsc

---

到目前为止，最简单的方法 - Sergio Figueiredo

Answer 6

我今天使用了这种方法，并且取得了成功。这个方法类似于其他答案，也是将内容转换为XML格式，只不过使用的是另一种方法。由于我没有在其他答案中看到提到FOR XML PATH，所以我想为完整性而添加它：

SELECT [COL_NVARCHAR_MAX]
  FROM [SOME_TABLE]
  FOR XML PATH(''), ROOT('ROOT')

这将生成一个有效的XML文件，其中包含所有行的内容，嵌套在外部的<ROOT></ROOT>元素中。每个单独行的内容都将包含在一个元素内，例如此示例中称为<COL_NVARCHAR_MAX>。可以通过AS使用别名更改其名称。

特殊字符如&、<或>将转换为它们各自的实体。因此，根据需要对结果进行的操作，您可能需要将<、>和&转换回它们的原始字符。

编辑

我刚意识到也可以使用FOR XML指定CDATA。虽然我认为有点麻烦。这样做：

SELECT 1 as tag, 0 as parent, [COL_NVARCHAR_MAX] as [COL_NVARCHAR_MAX!1!!CDATA]
  FROM [SOME_TABLE]
  FOR XML EXPLICIT, ROOT('ROOT')

- takrl

---

非常简单而有效的解决方案。 - Ben Osborne

Answer 7

使用Visual Studio Code SQL Server插件。它对JSON非常有用。

• 微软文档：Visual Studio Code的SQL Server扩展
• 扩展程序：SQL Server (mssql)

- roncansan

Answer 8

PowerShell替代方案

这是一篇旧文章，我已经阅读了答案。但我发现从SSMS输出多行大文本字段仍然有点困难。我最终编写了一个小的C#程序来满足我的需求，但随后想到可能可以使用命令行完成。事实证明，使用PowerShell相当容易。

首先，在管理权限的PowerShell中安装SqlServer模块。

Install-Module -Name SqlServer

使用 Invoke-Sqlcmd 运行您的查询：

$Rows = Invoke-Sqlcmd -Query "select BigColumn from SomeTable where Id = 123" `
    -MaxCharLength 2147483647 -ConnectionString $ConnectionString

这将返回一个行数组，您可以按以下方式将其输出到控制台：

$Rows[0].BigColumn

或者按照以下方式将输出保存到文件中：

$Rows[0].BigColumn | Out-File -FilePath .\output.txt -Encoding UTF8

结果是将美丽的、未被截断的文本写入文件以供查看/编辑。我相信还有类似的命令可以将文本保存回SQL Server，尽管这似乎是另一个问题。

编辑：事实证明，@dvlsc提供了一种将此方法描述为次要解决方案的答案。我认为因为它被列为次要答案，所以我第一次错过了它。我将保留我的答案，重点关注PowerShell方法，但至少想给予应有的荣誉。

- dana

Answer 9

如果您只需要查看它，我使用了这个：

print cast(dbo.f_functiondeliveringbigformattedtext(seed) as text)

最终结果是我在SMSS的消息窗口中获得了换行和所有内容。 当然，它只允许一个单元格 - 如果你想从多行中做一个单元格，你可以这样做：

declare @T varchar(max)=''
select @T=@T
       + isnull(dbo.f_functiondeliveringbigformattedtext(x.a),'NOTHINGFOUND!')
       + replicate(char(13),4)
from x -- table containing multiple rows and a value in column a
print @T

我使用这个工具来验证由SQL代码生成的JSON字符串。否则，它们很难阅读！

- CDenby

Answer 10

备选方案1：右击以复制单元格，粘贴到文本编辑器中（希望支持 utf-8）

备选方案2：右击并导出为 CSV 文件

备选方案3：使用 SUBSTRING 函数来可视化列的部分内容。例如：

SELECT SUBSTRING(fileXml,2200,200) FROM mytable WHERE id=123456

- Don G.

---

复制/粘贴会截断数据。只有“另存为”选项可以正常工作。 - binki