[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Need simple way to convert all nchar to nvarchar

Posted on 2011-04-21
Medium Priority
Last Modified: 2012-05-11
I am trying to change all of my fields on the database that are of type "nchar" to "nvarchar", but I'm finding this a much more difficult task than I thought it would be--unless I'm just missing something.   I am able to get all of my nchar fields, but many of them are tied to indexes, so when I run my conversion, it errors on any field that has an index tied to it.

Is there a way I could convert these fields easily to nvarchar and make sure the indexes are put back intact once it's done converting the fields without having to do them one by one?   That could take forever.

And in case you're wondering, there is good reason for the conversion to nvarchar, needless to say, space.
Question by:saturation
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35441342

Have you thought of scripting the database,
Changing the datatypes in the script and recreating the database then just import data from the old database

Expert Comment

ID: 35445536
If you don't want to script the entire thing out like @ewangoya mentioned script it out.

Right click on the database. Tasks->Generate Scripts. On the selection screen make sure that CREATE is set to "true" and that "Script (Full Text)Indexes" is set to "true". Then select the tables you want to generate the indexes for.

Do a find/replace for "CREATE INDEX" to "DROP INDEX". Run the drops. Make your alter statement changes and the undo the DROP INDEX and run the creates
LVL 43

Accepted Solution

Eugene Z earned 2000 total points
ID: 35456511
you can script your table
create as a new named with nvarchar adjustments
 pump data from old table to new one
insert into new
select * from old one

add from old table constrains indexes

or try to check what is your clusterd index and what constrains have this field
script them for create

and drop (as per earlier posts) change datatype
and add these indexes constrains back

--use below system procs to see what your table has
sp_helpindex yourtablename
sp_help  yourtablename


Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question