Sunday, April 19, 2009

change database columns from varchar to nvarchar if not already nvarchar

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



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


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 when syc.length >= 4000 Or syc.length = -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'

-- Note: You will need another script for converting text to ntext !!!!


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.

======================================

when you alter the field from varchar to nvarchar, you might encounter following error message:

Cannot alter or drop column 'title' because it is enabled for Full-Text Search

Use following script to select all the fields that have full text index enabled.


select
c.name as 'Table',b.name as 'Field'
--c.*, a.*, b.*
from sys.fulltext_index_columns a
join sys.columns b
on a.object_id=b.object_id
and a.column_id=b.column_id
join sys.objects c
on a.object_id=c.object_id


Alter table script: This script is for generating disable full text search scripts.

select
'ALTER FULLTEXT INDEX ON dbo.' + c.name + ' DROP (' + b.name + ') WITH NO POPULATION;'
--c.*, a.*, b.*
from sys.fulltext_index_columns a
join sys.columns b
on a.object_id=b.object_id
and a.column_id=b.column_id
join sys.objects c
on a.object_id=c.object_id

No comments: