Convert all NCHAR columns to NVARCHAR columns in a database or table

Question

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.

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.

Answer 1

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

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.

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