如何在SQL查询中轻松将所有ntext字段转换为nvarchar(max)？

Question

我希望能够编写一个SQL查询，不需要枚举列名，返回所有列和任何ntext列转换为varchar(max)。我想知道是否有巧妙的方法可以做到这一点。

这样做很好，因为我可以在这些查询上使用基于比较的运算符，如UNION、EXCEPT等。但是，由于netxt列无法进行比较，所以在使用这些运算符时会失败。

我的当前想法是：创建一个函数来构建动态SQL查询。类似于这个链接中的内容：http://lotsacode.wordpress.com/2010/03/23/sql-server-ntext-cannot-be-selected-as-distinct/ 是否有更好的方法？

感谢您的回答！

- unnknown

---

这对我有用：.http://www.bartread.com/2014/09/17/convert-all-ntext-columns-to-nvarcharmax-sql-server/. 我修改了脚本的INSERT部分为WHERE o.type = 'U' AND c.system_type_id IN (99,35);。 - CAD bloke

Answer 1

NTEXT将在未来的SQL Server版本中被移除（连同Image和Text一起），为什么不果断采取行动，将你的列更改为NVARCHAR(MAX)呢？这可能需要一些成本，但它很有可能是值得的：

ALTER TABLE dbo.T ALTER COLUMN NTextColumn NVARCHAR(MAX) NULL; -- OR NOT NULL

您可以使用以下方法生成和执行整个数据库的脚本：

DECLARE @SQL NVARCHAR(MAX) = 
    (   SELECT  'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' + 
                                QUOTENAME(OBJECT_NAME(object_id)) + 
                                ' ALTER COLUMN ' + QUOTENAME(Name) + 
                                ' NVARCHAR(MAX) ' + 
                                CASE WHEN is_nullable = 0 THEN 'NOT' ELSE '' END + 
                                ' NULL;' + CHAR(13) + 'GO' + CHAR(13)
        FROM    sys.columns
        WHERE   system_type_id = 99 --NTEXT
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)');

EXECUTE sp_executesql @SQL;

- GarethD

---

好的观点。不幸的是，我在公司中的地位不够高，无法做出那样的决定 :) - unnknown

那么告诉那些在图腾柱上高层的人来做出改变，如果他们已经达到了如此崇高的地位，他们应该会认识到你的建议是好的吧？ - GarethD

任何其他的解决方案都只是把问题藏起来而已。 - GarethD

惊人的脚本，只需"做出更改"，+1 - RemarkLima

如果 is_nullable=0，则返回“NOT”。 - sebeid

Answer 2

我用这个光标（很遗憾，这里没有集合式的替代方法）来实现了这一点：

DECLARE TableCursor CURSOR FAST_FORWARD 
FOR
    SELECT 
        t.Name,
        c.name,
        c.is_nullable, 
        typ.user_type_id
    FROM 
        sys.columns c 
    INNER JOIN 
        sys.tables t ON c.object_id = t.object_id
    INNER JOIN 
        sys.types typ ON c.system_type_id = typ.system_type_id
    WHERE   
        typ.name IN ('text', 'ntext')    -- user_type_id: text = 35, ntext = 99 

DECLARE @TableName sysname, @ColumnName sysname, @IsNullable BIT, @TypeID INT

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName, @ColumnName, @IsNullable, @TypeID

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @Stmt NVARCHAR(999)

    SET @Stmt = 'ALTER TABLE dbo.[' + @TableName + '] ALTER COLUMN [' + @ColumnName + '] ' + 
              CASE @TypeID 
                WHEN 35 THEN ' VARCHAR(MAX) '
                WHEN 99 THEN ' NVARCHAR(MAX) '
              END  + 
                CASE WHEN @IsNullable = 1 THEN 'NULL' ELSE 'NOT NULL' END

    PRINT @Stmt
    EXEC (@Stmt)

    FETCH NEXT FROM TableCursor INTO @TableName, @ColumnName, @IsNullable, @TypeID
END

CLOSE TableCursor
DEALLOCATE TableCursor

我简化了我的代码，假设所有的表都在dbo模式中 - 如果你的情况不是这样，你也需要从sys.schema目录视图中包括模式。

运行此代码将一劳永逸地将所有的text转换为varchar(max)，并将所有的ntext转换为nvarchar(max)，让你的text/ntext问题消失得无影无踪！ :-)

- marc_s

---

不要忘记更新文本字段以避免性能下降。在这里查看。我在修改表格后插入了以下行：SET @Stmt = 'UPDATE dbo.[' + @TableName + '] SET [' + @ColumnName + '] = [' + @ColumnName + ']' PRINT @Stmt EXEC (@Stmt) - cjbarth

@cjbarth：哦，你链接的那篇文章很有意思 - 谢谢提醒！ - marc_s

Answer 3

这是我修改过的GarethD上面的答案。由于SQL找不到某些表，所以我使用了sys.tables与sys.columns连接。此外，is_nullable行是不正确的（如果字段不可为空，则将其设置为NOT NULL）。

DECLARE @SQL NVARCHAR(MAX) = ' ';

SELECT  @SQL = @SQL + ' ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(sys.columns.object_id)) + '.' + 
                        QUOTENAME(OBJECT_NAME(sys.columns.object_id)) + 
                        ' ALTER COLUMN ' + QUOTENAME(sys.columns.Name) + 
                        ' NVARCHAR(MAX) ' + 
                        CASE WHEN is_nullable = 0 THEN 'NOT NULL' ELSE '' END 

FROM    sys.Tables
inner join sys.columns on sys.tables.object_id = sys.columns.object_id
WHERE   sys.columns.system_type_id = 99 ; --NTEXT 

EXECUTE sp_executesql @SQL;
GO

- Daniel Mitchell