changing varchar to nvarchar - Erland Sommarskog

changing varchar to nvarchar - Erland Sommarskog
11-Nov-07 10:24:28


Better Article - change database columns from varchar to nvarchar if not already nvarchar

Hans - DiaGraphIT - (HansDiaGraphIT@) writes:

Run any of these SELECT:

-- SQL 2005
SELECT 'ALTER TABLE ' + quotename(o.name) +
' ALTER COLUMN ' + quotename(c.name) +
' nvarchar(' + ltrim(str(c.max_length)) + ') ' +
CASE c.is_nullable
WHEN 1 THEN ' NULL'
WHEN 0 THEN ' NOT NULL'
END
FROM  sys.objects o
JOIN  sys.columns c ON o.object_id = c.object_id
JOIN  sys.types t ON c.system_type_id = t.system_type_id
WHERE t.name = 'varchar'
ORDER BY o.name, c.name

-- SQL 2000
SELECT 'ALTER TABLE ' + quotename(o.name) +
' ALTER COLUMN ' + quotename(c.name) +
' nvarchar(' + ltrim(str(c.length)) + ') ' +
CASE columnproperty(o.id, c.name, 'AllowsNull')
WHEN 1 THEN ' NULL'
WHEN 0 THEN ' NOT NULL'
END
FROM  sysobjects o
JOIN  syscolumns c ON o.id = c.id
JOIN  systypes t ON c.xtype = t.xtype
WHERE t.name = 'varchar'
AND o.xtype = 'U'
ORDER BY o.name, c.name

Copy and past the result. However, there is a reason that I recommended
rebuilding from scripts. ALTER TABLE only takes one ALTER COLUMN, so
if there are several varchar columns in the same table, SQL Server
have to rebuild the tables several times. Since nvarchar takes up twice
the space of varchar, this is no simple metadata change. For large
tables, this could take a long time. Building a new database may be
faster route.


But maybe some of the columns are indexed? In such case, you need to
drop these indexes first.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx