What Was I Thinking?

Follies & Foils of .NET Development
posts - 95 , comments - 352 , trackbacks - 0

Convert VARCHAR() columns to NVARCHAR()

We recently underwent an upgrade that required us to change our database columns from varchar to NVarchar, to support unicode characters.

Digging through the internet, I found a base script which I modified to handle reserved word table names, and maintain the NULL/NotNull constraint of the columns.

 

I Ran this script

use NWOperationalContent – Your Catalog Name here

GO

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 + ') '+
        case  syc.isnullable when 1 then ' NULL' ELSE ' NOT NULL' 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'

 

which produced a series of ALTER statements which I could then execute the tables.  In some cases I had to drop indexes, alter the tables, and re-create the indexes.  There might have been a better way to do that, but manually dropping them got the job done.

 

use NWMerchandisingContent
GO
ALTER TABLE Locale Drop Constraint PK_Locale
ALTER TABLE Country DROP CONSTRAINT PK_Country

GO
ALTER TABLE dbo.[Campaign]  ALTER COLUMN [ActorKey] NVARCHAR(200)  NOT NULL;
ALTER TABLE dbo.[BundleLocalization]  ALTER COLUMN [Locale] NVARCHAR(8)  NOT NULL;
ALTER TABLE dbo.[BundleLocalization]  ALTER COLUMN [UnitOfmeasure] NVARCHAR(200)  NULL;
ALTER TABLE dbo.[BundleLocalization]  ALTER COLUMN [ActorKey] NVARCHAR(200)  NOT NULL;
ALTER TABLE dbo.[BundleComponentLocalization]  ALTER COLUMN [Locale] NVARCHAR(8)  NOT NULL;
ALTER TABLE dbo.[BundleComponentLocalization]  ALTER COLUMN [Imperative] NVARCHAR(MAX)  NULL;
ALTER TABLE dbo.[BundleComponentLocalization]  ALTER COLUMN [Instructions] NVARCHAR(MAX)  NULL;
ALTER TABLE dbo.[BundleComponentLocalization]  ALTER COLUMN [ActorKey] NVARCHAR(200)  NOT NULL;
ALTER TABLE dbo.[BundleComponent]  ALTER COLUMN [ActorKey] NVARCHAR(200)  NOT NULL;
ALTER TABLE dbo.[Bundle]  ALTER COLUMN [ActorKey] NVARCHAR(200)  NOT NULL;
ALTER TABLE dbo.[Banner]  ALTER COLUMN [ActorKey] NVARCHAR(200)  NOT NULL;
ALTER TABLE dbo.[Video]  ALTER COLUMN [Link] NVARCHAR(512)  NOT NULL;
ALTER TABLE dbo.[Video]  ALTER COLUMN [ActorKey] NVARCHAR(200)  NOT NULL;
ALTER TABLE dbo.[ProductUsage]  ALTER COLUMN [VideoLink] NVARCHAR(512)  NOT NULL;
ALTER TABLE dbo.[ProductUsage]  ALTER COLUMN [ActorKey] NVARCHAR(200)  NOT NULL;
ALTER TABLE dbo.[Thumbnail]  ALTER COLUMN [ActorKey] NVARCHAR(200)  NOT NULL;
ALTER TABLE dbo.[SkuLocalization]  ALTER COLUMN [Locale] NVARCHAR(8)  NOT NULL;
ALTER TABLE dbo.[SkuLocalization]  ALTER COLUMN [UnitOfMeasure] NVARCHAR(150)  NOT NULL;
ALTER TABLE dbo.[SkuLocalization]  ALTER COLUMN [SwatchColor] NVARCHAR(50)  NOT NULL;

etc..

GO
ALTER TABLE Locale ADD CONSTRAINT PK_Locale PRIMARY KEY (LocaleId)
ALTER TABLE Country ADD CONSTRAINT PK_Country PRIMARY KEY (CountryId)

Note that this alter is non-destructive to the data.

 

Hope this helps.

Print | posted on Wednesday, September 26, 2012 6:46 PM | Filed Under [ SQL ]

Feedback

Gravatar

# Convert VARCHAR() columns to NVARCHAR()

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
11/6/2012 6:30 AM | hongyijig
Post A Comment
Title:
Name:
Email:
Comment:
Verification:
 

Powered by: