Converting Datatype Char To Nvarchar

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

Solution 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 ASVARCHAR(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.

Solution 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.

selectcast((select top 1 COLUMN_NAME 
from INFORMATION_SCHEMA.COLUMNS
where DATA_TYPE = 'char') asnvarchar(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.