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

Question

Hello, 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

The following query should get you what you need:

IF EXISTS 
  (SELECT *
   FROM sysobjects syo
   JOIN syscolumns syc ON
     syc.id = syo.id
   JOIN systypes syt ON
     syt.xtype = syc.xtype
   WHERE 
     syt.name = 'nvarchar' AND
     syo.name = 'MY TABLE NAME' AND
     syc.name = 'MY COLUMN NAME')
BEGIN
   ALTER ...
END

Answer 2

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.

Answer 3

Works great, except i had a few columns with spaces in their names - so added this:

' ALTER COLUMN [' + syc.name + '] NVARCHAR(' (dot dot dot)

Answer 4

Hi All,

Thank you for useful query, however it doesn't work in case if there are depending objects like foreign keys or stored procedures. Are there anybody who run into same issue and found solution.

Answer 5

disable all foreign keys and enable them again afterwards: http://decipherinfosys.wordpress.com/2008/02/20/disableenable-foreign-key-and-check-constraints-in-sql-server/

Answer 6

You may also find it helpful to look at other answers to the same question here:

http://stackoverflow.com/questions/1842502/how-to-change-every-nvarchar-column-to-varchar