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

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.

The issue with Josef's answer is that it would change NOT NULL fields to NULL after executing the queries. The following manipulation fixes it:

SELECT cmd = 'alter table [' + c.table_schema + '].[' + c.table_name 
 + '] alter column [' + c.column_name + '] nvarchar('
 +CASE WHEN CHARACTER_MAXIMUM_LENGTH<=4000
       THEN CAST(CHARACTER_MAXIMUM_LENGTH as varchar(10)) ELSE 'max' END+')' 
 + CASE WHEN IS_NULLABLE='NO' THEN ' NOT NULL' ELSE '' END,*
FROM information_schema.columns c
WHERE c.data_type='varchar' 
ORDER BY CHARACTER_MAXIMUM_LENGTH desc

Credits to Igor's answer

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