将varchar更改为nvarchar以用于数据库中的所有表列

Is there a way to fully traverse all tables in the database and changing all the varchar datatype to nvarchar datatype?

有没有办法完全遍历数据库中的所有表并将所有varchar数据类型更改为nvarchar数据类型？

The simple SQL below does not work as intended due to constraints from indexing, unique keys and what not.

由于索引，唯一键和不支持的约束，下面的简单SQL无法正常工作。

ALTER TABLE [CUS].[Customers] 
    ALTER COLUMN [CustomerNo] NVARCHAR(500);

I tried changing them from SSMS via the GUI way and the profiler actually shows what it does to truly change the datatype.

我尝试通过GUI方式从SSMS更改它们，并且探查器实际上显示了它真正改变数据类型的作用。

Step 1: it creates a temporary table, with nvarchar column changed from varchar
第1步：它创建一个临时表，其中nvarchar列已从varchar更改
Step 2: set some lock escalation (no idea what this is)
第2步：设置一些锁升级（不知道这是什么）
Step 3: inserts all data from original table into the temporary table
步骤3：将原始表中的所有数据插入临时表
Step 4: drops original table
第4步：删除原始表
Step 5: renames temporary table to original table's name
第5步：将临时表重命名为原始表的名称
Step 6: adds the constraints
第6步：添加约束
Step 7: adds the indexes
第7步：添加索引

This involves a lot of steps and it is quite cumbersome if I were to do it manually.

这涉及很多步骤，如果我手动完成它会非常麻烦。

Can anyone suggest a more flexible solution to my problem?

任何人都可以建议更灵活的解决方案吗？

1 个解决方案

#1

Alternatively you could:

或者你可以：

script whole database without data
脚本整个数据库没有数据
Alter column definitions from VARCHAR to NVARCHAR
将列定义从VARCHAR更改为NVARCHAR
Use either SSIS or SQL to transfer data
使用SSIS或SQL传输数据

Pros:

优点：

it will have potentially lesser impact on source database
它对源数据库的影响可能较小
you could minimize negative impact even more you can run this on separate server
你可以减少负面影响，甚至可以在单独的服务器上运行它

Cons:

缺点：

you would have to transfer all data, which takes time
你需要传输所有数据，这需要时间
you would have to make sure you have enough disk space on server for database data, index data and log file
您必须确保服务器上有足够的磁盘空间用于数据库数据，索引数据和日志文件

Depending on transfer method you would choose and size of your tables, log file might grow huge even if you are in simple recovery mode. This is worst case scenario when you transfer a lot of data in one huge transaction.

根据您选择的传输方法和表的大小，即使您处于简单恢复模式，日志文件也可能会变大。当您在一个巨大的事务中传输大量数据时，这是最糟糕的情况。

As a side comment: I agree with @Igor - do not change datatype if you absolutely have to.

作为旁注：我同意@Igor - 如果你绝对必须改变数据类型。

#1