Change all VARCHAR columns to NVARCHAR

Question

I have about 200 columns in my database with VARCHAR type which is unable to store the rupee symbol. Now I have to change the datatype of all the columns from VARCHAR to NVARCHAR.

Can anyone please tell me the short way to accomplish this? And why does the VARCHAR support the pound sign and not the rupee sign? I'm asking because I have to change the pound symbol to rupee symbol.

Answer 1

You can view all the columns, their data types and the table they belong using the ff query:

SELECT 
    t.name AS table_name,
    c.name AS column_name,
    tp.name AS data_type,
    c.max_length,
    c.is_nullable
FROM sys.tables AS t
INNER JOIN sys.columns c 
    ON t.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.types tp
    ON tp.user_type_id = c.user_type_id
WHERE tp.name = 'varchar'

From the query above, you want to generate a dynamic sql that will change all your VARCHAR columns to NVARCHAR.

DECLARE @sql AS VARCHAR(MAX) = ''

SELECT @sql = @sql 
    + 'ALTER TABLE ' + table_name 
    + ' ALTER COLUMN ' + column_name + ' NVARCHAR(' 
        + CASE WHEN max_length <> - 1 THEN CAST(max_length AS VARCHAR(10))  ELSE 'MAX' END + ')'
        + CASE WHEN is_nullable = 1 THEN ' NULL' ELSE '' END
        + ';' + CHAR(10)
FROM (
    SELECT 
        t.name AS table_name,
        c.name AS column_name,
        tp.name AS data_type,
        c.max_length,
        c.is_nullable
    FROM sys.tables AS t
    INNER JOIN sys.columns c 
        ON t.OBJECT_ID = c.OBJECT_ID
    INNER JOIN sys.types tp
        ON tp.user_type_id = c.user_type_id
    WHERE tp.name = 'varchar'
)t

PRINT @sql
EXEC(@sql)

Answer 2

I met this issue today and Felix's answer worked quite well with one exception- if the column has default value defined. I researched a little bit and came up with the following script that drops and restores the default value.

DECLARE @sql AS VARCHAR(MAX) = ''

SELECT @sql = @sql
    + CASE WHEN default_id>0 THEN 'ALTER TABLE '+table_name+' DROP CONSTRAINT '+OBJECT_NAME(default_id) +';'+CHAR(10) ELSE '' END
    + 'ALTER TABLE ' + table_name 
    + ' ALTER COLUMN ' + column_name + ' NVARCHAR(' 
        + CASE WHEN (max_length <> - 1 AND max_length<4001) THEN CAST(max_length AS VARCHAR(10))  ELSE 'MAX' END + ')'
        + CASE WHEN is_nullable = 1 THEN ' NULL' ELSE '' END
        + ';' + CHAR(10)
    + CASE WHEN default_id>0 THEN 'ALTER TABLE '+table_name+' ADD DEFAULT '+default_value+' FOR '+column_name +';'+CHAR(10) ELSE '' END
FROM (
    SELECT 
        t.name AS table_name,
        c.name AS column_name,
        tp.name AS data_type,
        c.max_length,
        c.default_object_id AS default_id,
        OBJECT_DEFINITION(c.default_object_id) AS default_value,
        c.is_nullable
    FROM sys.tables AS t
    INNER JOIN sys.columns c 
        ON t.OBJECT_ID = c.OBJECT_ID
    INNER JOIN sys.types tp
        ON tp.user_type_id = c.user_type_id
    WHERE tp.name = 'varchar'
)t

PRINT @sql
EXEC(@sql)