如何修正数据库中误设为nvarchar的所有列的数据类型？

针对把错误设为nvarchar的列修正为BIT、INT、DECIMAL、DATE、CHAR类型的需求，你提到的两种思路都具备可行性，以下是具体实现方案：

思路一：带CASE判断的动态SQL（预检测+批量生成修改语句） #

这种方式先批量验证每个nvarchar列的数据是否符合目标类型规则，再生成对应的ALTER TABLE语句，适合数据质量较高、无大量脏数据的场景。

实现步骤： #

1. 遍历数据库中所有nvarchar类型的列，排除原本就应该是nvarchar的列（比如存储变长文本的业务字段）
2. 对每个列依次验证是否符合BIT、INT、DECIMAL、DATE、CHAR的格式规则
3. 生成对应的修改列类型SQL语句，人工确认后执行

示例SQL： #

DECLARE @sql NVARCHAR(MAX) = ''

SELECT @sql += 
    CASE
        -- 验证BIT类型：值仅为'0'/'1'或NULL
        WHEN EXISTS (SELECT 1 FROM QUOTENAME(c.TABLE_SCHEMA)+'.'+QUOTENAME(c.TABLE_NAME) 
                     WHERE QUOTENAME(c.COLUMN_NAME) NOT IN ('0','1') AND QUOTENAME(c.COLUMN_NAME) IS NOT NULL) = 0
        THEN 'ALTER TABLE '+QUOTENAME(c.TABLE_SCHEMA)+'.'+QUOTENAME(c.TABLE_NAME)+' ALTER COLUMN '+QUOTENAME(c.COLUMN_NAME)+' BIT;' + CHAR(13) + CHAR(10)
        -- 验证INT类型：所有值可转换为INT
        WHEN EXISTS (SELECT 1 FROM QUOTENAME(c.TABLE_SCHEMA)+'.'+QUOTENAME(c.TABLE_NAME) 
                     WHERE TRY_CAST(QUOTENAME(c.COLUMN_NAME) AS INT) IS NULL AND QUOTENAME(c.COLUMN_NAME) IS NOT NULL) = 0
        THEN 'ALTER TABLE '+QUOTENAME(c.TABLE_SCHEMA)+'.'+QUOTENAME(c.TABLE_NAME)+' ALTER COLUMN '+QUOTENAME(c.COLUMN_NAME)+' INT;' + CHAR(13) + CHAR(10)
        -- 验证DECIMAL（示例精度18,2）：所有值可转换为DECIMAL(18,2)
        WHEN EXISTS (SELECT 1 FROM QUOTENAME(c.TABLE_SCHEMA)+'.'+QUOTENAME(c.TABLE_NAME) 
                     WHERE TRY_CAST(QUOTENAME(c.COLUMN_NAME) AS DECIMAL(18,2)) IS NULL AND QUOTENAME(c.COLUMN_NAME) IS NOT NULL) = 0
        THEN 'ALTER TABLE '+QUOTENAME(c.TABLE_SCHEMA)+'.'+QUOTENAME(c.TABLE_NAME)+' ALTER COLUMN '+QUOTENAME(c.COLUMN_NAME)+' DECIMAL(18,2);' + CHAR(13) + CHAR(10)
        -- 验证DATE：所有值可转换为DATE
        WHEN EXISTS (SELECT 1 FROM QUOTENAME(c.TABLE_SCHEMA)+'.'+QUOTENAME(c.TABLE_NAME) 
                     WHERE TRY_CAST(QUOTENAME(c.COLUMN_NAME) AS DATE) IS NULL AND QUOTENAME(c.COLUMN_NAME) IS NOT NULL) = 0
        THEN 'ALTER TABLE '+QUOTENAME(c.TABLE_SCHEMA)+'.'+QUOTENAME(c.TABLE_NAME)+' ALTER COLUMN '+QUOTENAME(c.COLUMN_NAME)+' DATE;' + CHAR(13) + CHAR(10)
        -- 验证CHAR（示例长度10）：所有值长度固定为10（根据实际场景调整）
        WHEN EXISTS (SELECT 1 FROM QUOTENAME(c.TABLE_SCHEMA)+'.'+QUOTENAME(c.TABLE_NAME) 
                     WHERE LEN(QUOTENAME(c.COLUMN_NAME)) != 10 AND QUOTENAME(c.COLUMN_NAME) IS NOT NULL) = 0
        THEN 'ALTER TABLE '+QUOTENAME(c.TABLE_SCHEMA)+'.'+QUOTENAME(c.TABLE_NAME)+' ALTER COLUMN '+QUOTENAME(c.COLUMN_NAME)+' CHAR(10);' + CHAR(13) + CHAR(10)
    END
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.DATA_TYPE = 'nvarchar' 
AND c.COLUMN_NAME NOT IN ('无需修改的nvarchar列名1', '无需修改的nvarchar列名2') -- 排除业务需要的nvarchar列

-- 打印生成的SQL，确认无误后再执行
PRINT @sql
-- EXEC sp_executesql @sql

思路二：带错误处理的逐步转换（尝试转换+清理脏数据） #

这种方式适合存在少量脏数据的场景，通过TRY_CAST/TRY_CONVERT尝试转换，先标记或清理脏数据，再修改列类型。

实现步骤： #

1. 给目标列添加临时列，尝试将原nvarchar列的值转换为目标类型存入临时列
2. 筛选出转换失败的行（临时列为NULL但原列非NULL），处理脏数据
3. 确认所有数据转换成功后，删除原列并将临时列重命名为原列名

示例SQL（以INT类型为例）： #

-- 1. 添加临时列
ALTER TABLE [YourTable] ADD [YourColumn_Temp] INT

-- 2. 尝试转换数据
UPDATE [YourTable]
SET [YourColumn_Temp] = TRY_CAST([YourColumn] AS INT)

-- 3. 查询转换失败的脏数据，进行修正或标记
SELECT * FROM [YourTable] WHERE [YourColumn_Temp] IS NULL AND [YourColumn] IS NOT NULL

-- 4. 处理完脏数据后，替换原列
ALTER TABLE [YourTable] DROP COLUMN [YourColumn]
EXEC sp_rename '[YourTable].[YourColumn_Temp]', '[YourColumn]', 'COLUMN'

注意事项 #

• 先备份：操作前务必全量备份数据库，避免数据丢失
• 测试环境验证：所有操作先在测试环境跑一遍，确认结果符合预期再推生产
• 事务包裹：修改列类型时用事务包裹，出错可直接回滚
• 低峰期执行：大表操作选业务低峰期，避免长时间锁表影响业务

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