如何将SQL Server中表和视图的ntext列转为nvarchar(max)

一、处理表的ntext列（优化原有代码） #

原有代码可正常处理表，但建议加上架构名避免同表名不同架构的冲突，优化后的代码如下：

-- 创建临时表存储需要修改的表和列
CREATE TABLE #TablesToModify
(
    SchemaName NVARCHAR(255),
    TableName NVARCHAR(255),
    ColumnName NVARCHAR(255)
)

-- 填充临时表：所有包含ntext类型列的表
INSERT INTO #TablesToModify
SELECT c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.DATA_TYPE = 'ntext'

-- 遍历修改表列类型
DECLARE @SchemaName NVARCHAR(255)
DECLARE @TableName NVARCHAR(255)
DECLARE @ColumnName NVARCHAR(255)
DECLARE @AlterSql NVARCHAR(MAX)

DECLARE curTables CURSOR FOR
SELECT SchemaName, TableName, ColumnName
FROM #TablesToModify

OPEN curTables
FETCH NEXT FROM curTables INTO @SchemaName, @TableName, @ColumnName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @AlterSql = 'ALTER TABLE [' + @SchemaName + '].[' + @TableName + '] ALTER COLUMN [' + @ColumnName + '] NVARCHAR(MAX)'
    EXEC sp_executesql @AlterSql

    FETCH NEXT FROM curTables INTO @SchemaName, @TableName, @ColumnName
END

CLOSE curTables
DEALLOCATE curTables

DROP TABLE #TablesToModify

二、处理视图的ntext列 #

视图无法直接用ALTER VIEW ALTER COLUMN修改列类型，需分两种情况处理：

1. 视图列直接引用基础表的ntext列：当基础表的列已改为nvarchar(max)，只需刷新视图即可同步列类型
2. 视图列通过表达式生成ntext类型：需要修改视图定义，将ntext替换为nvarchar(max)

以下是整合的视图处理代码：

-- 创建临时表存储需要处理的视图
CREATE TABLE #ViewsToProcess
(
    SchemaName NVARCHAR(255),
    ViewName NVARCHAR(255),
    ColumnName NVARCHAR(255),
    ViewDefinition NVARCHAR(MAX)
)

-- 填充临时表：所有包含ntext类型列的视图
INSERT INTO #ViewsToProcess
SELECT 
    v.TABLE_SCHEMA,
    v.TABLE_NAME,
    c.COLUMN_NAME,
    OBJECT_DEFINITION(OBJECT_ID(QUOTENAME(v.TABLE_SCHEMA) + '.' + QUOTENAME(v.TABLE_NAME))) AS ViewDefinition
FROM INFORMATION_SCHEMA.COLUMNS c
JOIN INFORMATION_SCHEMA.VIEWS v ON c.TABLE_SCHEMA = v.TABLE_SCHEMA AND c.TABLE_NAME = v.TABLE_NAME
WHERE c.DATA_TYPE = 'ntext'

-- 遍历处理视图
DECLARE @ViewSchema NVARCHAR(255)
DECLARE @ViewName NVARCHAR(255)
DECLARE @ViewColName NVARCHAR(255)
DECLARE @ViewDef NVARCHAR(MAX)
DECLARE @AlterViewSql NVARCHAR(MAX)

DECLARE curViews CURSOR FOR
SELECT SchemaName, ViewName, ColumnName, ViewDefinition
FROM #ViewsToProcess

OPEN curViews
FETCH NEXT FROM curViews INTO @ViewSchema, @ViewName, @ViewColName, @ViewDef

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 先尝试刷新视图（同步基础表修改后的列类型）
    EXEC sp_refreshview @viewname = QUOTENAME(@ViewSchema) + '.' + QUOTENAME(@ViewName)

    -- 检查刷新后列类型是否仍为ntext，若是则说明是表达式生成的ntext，需修改视图定义
    IF EXISTS (
        SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = @ViewSchema AND TABLE_NAME = @ViewName AND COLUMN_NAME = @ViewColName AND DATA_TYPE = 'ntext'
    )
    BEGIN
        -- 替换视图定义中的ntext为nvarchar(max)
        SET @AlterViewSql = REPLACE(@ViewDef, 'ntext', 'NVARCHAR(MAX)')
        -- 替换CREATE VIEW为ALTER VIEW
        SET @AlterViewSql = STUFF(@AlterViewSql, CHARINDEX('CREATE VIEW', @AlterViewSql), 11, 'ALTER VIEW')
        EXEC sp_executesql @AlterViewSql
    END

    FETCH NEXT FROM curViews INTO @ViewSchema, @ViewName, @ViewColName, @ViewDef
END

CLOSE curViews
DEALLOCATE curViews

DROP TABLE #ViewsToProcess

注意事项 #

• 执行前务必备份数据库，避免意外数据丢失
• 确保有足够权限：ALTER TABLE、ALTER VIEW、VIEW DEFINITION等权限
• 若视图是加密的（WITH ENCRYPTION），OBJECT_DEFINITION无法获取定义，需手动处理
• 复杂视图可能存在依赖关系，修改前建议用sp_depends或sys.dm_sql_referencing_entities检查依赖

内容的提问来源于stack exchange，提问作者Dmytro