将数据类型Char转换为Nvarchar [英] Converting datatype Char to Nvarchar

问题描述

我目前正试图将数据库中所有表中的所有列从SQL转换为nvarchar在SQL Server 2012中。

I am currently trying to convert all columns in all tables of my database from char to nvarchar in SQL server 2012.

我试图运行的查询是

select cast((select COLUMN_NAME 
from INFORMATION_SCHEMA.COLUMNS
where DATA_TYPE = 'char') as nvarchar(MAX))

不知道我是否应该通过INFORMATION_SCHEMA.COLUMNS更改数据类型，这是获取数据类型的最佳方式。

Not sure if I should be changing the data type through INFORMATION_SCHEMA.COLUMNS but I found this to be the best way to grab the datatype.

我当前得到的错误是：

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

有没有更好的方法来完成这个或一个绕过这个错误的方法，

Is there a better way to accomplish this or a way around this error, as it seems it doesn't like me changing datatypes to multiple rows at the same time.

推荐答案

从INFORMATION_SCHEMA.COLUMNS中选择是一个很好的方法来确定什么是数据类型列需要转换，

Selecting from INFORMATION_SCHEMA.COLUMNS is a good way to determine what columns need to be converted,

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE = 'CHAR';

但它不能用于直接更改列的数据类型。 ALTER TABLE 用于修改列数据类型：

But it can't be used to directly change a column's data type. ALTER TABLE is used to modify column data types:

ALTER TABLE [dbo].[TableName] ALTER COLUMN [ColumnName] NVARCHAR(50);

在使用时，请避免使用 NVARCHAR code>除非它是绝对必要的。确保您的数据类型的大小专门为该属性。如果您的 CHAR 列的大小已正确，请使用以下脚本生成 ALTER TABLE statement：

While you're at it, avoid using NVARCHAR(MAX) unless it's absolutely necessary. Make sure your data types are sized specifically for the attribute. If your CHAR columns are already sized correctly, use the following script to generate the ALTER TABLE statements:

SELECT  'ALTER TABLE ' +
        QUOTENAME(TABLE_SCHEMA) + '.' +
        QUOTENAME(TABLE_NAME) +
        ' ALTER COLUMN ' +
        QUOTENAME(COLUMN_NAME) +
        ' NVARCHAR(' +
        CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(4)) + ');'
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE   DATA_TYPE = 'char';

请记住，这只会生成ALTER TABLE语句，您需要复制结果请在新标签页中执行以更改数据类型。

Keep in mind that this only generates the ALTER TABLE statements, you'll need to copy the results and execute in a new tab to change the data types.

这篇关于将数据类型Char转换为Nvarchar的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！