We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Need simple way to convert all nchar to nvarchar

Medium Priority
365 Views
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.
Comment
Watch Question

Ephraim WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:

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

Commented:
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
SQL SERVER EXPERT
CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.