alter table - SQL Server - script to update database columns from varchar to nvarchar if not already nvarchar

Question

I am in a situation where I must update an existing database structure from varchar to nvarchar using a script. Since this script is run everytime a configuration application is run, I would rather determine if a column has already been changed to nvarchar and not perform an alter on the table. The databases which I must support are SQL Server 2000, 2005 and 2008.

Answer 1

You can run the following script which will give you a set of ALTER commands:

SELECT 'ALTER TABLE ' + isnull(schema_name(syo.id), 'dbo') + '.' +  syo.name 
    + ' ALTER COLUMN ' + syc.name + ' NVARCHAR(' + case syc.length when -1 then 'MAX' 
        ELSE convert(nvarchar(10),syc.length) end + ');'
   FROM sysobjects syo
   JOIN syscolumns syc ON
     syc.id = syo.id
   JOIN systypes syt ON
     syt.xtype = syc.xtype
   WHERE 
     syt.name = 'varchar' 
    and syo.xtype='U'

There are, however, a couple of quick caveats for you.

1. This will only do tables. You'll want to scan all of your sprocs and functions to make sure they are changed to NVARCHAR as well.
2. If you have a VARCHAR > 4000 you will need to modify it to be NVARCHAR(MAX)

But those should be easily doable with this template.

If you want this to run automagically you can set it in a WHILE clause.