将数据库或表中的所有 NCHAR 列转换为 NVARCHAR 列 [英] Convert all NCHAR columns to NVARCHAR columns in a database or table

问题描述

我有一个包含很多 NCHAR(n) 列的数据库.其中一些列是简单的属性，但有些也是主键或外键.

I have a database that contains a lot of NCHAR(n) columns. Some of these columns are simple properties, but some are also primary-keys or foreign keys.

表和列很多，测试数据量很大.

There are many tables and columns and a huge amount of test data.

将每个 NCHAR(n) 列转换为相同长度的 NVARCHAR(n) 列并在此过程中修剪其内容的最佳方法是什么?

What is the best way to convert every NCHAR(n) column to a NVARCHAR(n) column of the same length and trim its content while doing so?

如果您能想到比在设计器中更改列、记住列名并在脚本窗口中修剪更好的方法，请将其作为答案发布.

If you can think of anything better than changing the column in the designer, remembering the column name and trimming it in a script window, please post it as an answer.

推荐答案

我认为这样的事情可以解决问题(除非任何是主键，然后您会收到错误 - 我建议您手动执行主键在您的设计器中，因为要更改它们，您必须删除约束等，这有点棘手)...

Something like this would do the trick I think (unless any are primary keys and then you will get an error - I would suggest that you manually do the primary keys in the designer in your case because to alter them you have to drop the constraints etc and that gets a bit tricky)...

它获取所有 nchar 列的所有表名、列名和大小，并为每个列执行 alter 语句以更改列类型，然后执行修剪数据的 update.

It gets all the table names, column names and sizes for all the columns that are nchar and for each performs an alter statement to change the column type and then an update that trims the data.

可能有一种更高效的方法来做到这一点，但如果您只做一次，也许这会没问题(哦，将 databaseName 位更改为您的数据库名称)...

There may be a more performant way to do it but if you are only doing it once perhaps this will be ok (oh, change the databaseName bit to the name of your database)...

use databaseName

declare @tn nvarchar(128)
declare @cn nvarchar(128)
declare @ln int

declare @sql as nvarchar(1000)

declare c cursor for 
    select table_name,column_name,character_maximum_length 
    from information_schema.columns 
    where data_type ='nchar' and 
        TABLE_NAME not in (select TABLE_NAME from INFORMATION_SCHEMA.VIEWS)

open c
fetch next from c into @tn, @cn, @ln

while @@FETCH_STATUS = 0
begin

    set @sql = 'alter table ' + @tn + ' alter column ' 
        + @cn + ' nvarchar(' + convert(nvarchar(50), @ln) + ')'
    exec sp_executesql @sql

    set @sql = 'update ' + @tn + ' set ' + @cn + ' = LTRIM(RTRIM(' + @cn + '))'
    exec sp_executesql @sql

    fetch next from c into @tn, @cn, @ln
end

close c
deallocate c

这篇关于将数据库或表中的所有 NCHAR 列转换为 NVARCHAR 列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！