Converting datatype Char to Nvarchar

Question

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

the query I am trying to run is

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

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.

the error I am currently getting is:

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.

Answer 1

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';

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);

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';

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.

Answer 2

First things first. You are getting the error because the select returns more than one row and you are trying to cast to nvarchar(MAX) all the column_name. Any subquery that you might need to do it in the future must only return one row.

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

This will fix the error, but that is not what you are trying to do.

To update all the columns you need to do something based on the following command:

ALTER TABLE {TableName} ALTER COLUMN {ColumnName} NVARCHAR(size)

What you could do is to get the table_name and column_name from INFORMATION_SCHEMA.COLUMNS and build a dynamic script to update all the columns in the database from char to nvarchar.