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

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.

From stackoverflow Michelle

• 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
  

  From Peter

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

  DanM : This worked beautifully for me. Great little script!
  From Josef

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

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

  From boomhauer

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

  From Vadim911

• 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/

  From rvdurm

• 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

  From Tod1d