Change Collation

Question

Change Collation

shree

Say Hey Kid

Points: 702
More actions
May 6, 2009 at 7:54 am

#131219

Hi Guys
I Was on the Move of changing the Database collation from Windows to SQL. In that process i have found many Columns Declared column level Collation, how can i change them at a time.
i was doing this way, Backup and restore the database with different name and change the collation to SQL.
CHECK the column level collation if found change them by altering the table but it is taking long time to do so
Is there any script that can go through all the columns in syscolumns view and find out and fix the collation.
Need kind of script.like cursor.
or provideif there are any other methods can be done in different way.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

BU69 SSCrazy Points: 2784 More actions · Answer 1

I had to change collation on a lot of databases a while ago and I used this free open source utility, it saved me a lot of time and from the research I had done before I found the code it basically automats the same steps I was going through manually, it works anyway.

http://www.codeproject.com/KB/database/ChangeCollation.aspx

It's work checking and testing first and if you are going from binary or case sensitive to a normal collation as I was you need to get rid of duplicates otherwise it will fail several times as you work through it.

Good luck

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 2

I would do this:

1. Write a statement to change the collation for a single column (Alter Table...)

2. Write a statement to identify such columns in sys.columns or INFORMATION_SCHEMA.COLUMNS like:

select TABLE_NAME,COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS

where collation_name = 'your old collation name'

3. Based on 2 above queries, compose a query that will have the first one as fixed strings and instead of the table and column names use the output of the second query for example as:

select 'Alter Table ' + TABLE_NAME + ' Alter Column ' + COLUMN_NAME

+ ' the rest of your collation change statement goes here...in quotes '

from INFORMATION_SCHEMA.COLUMNS

where collation_name = 'your old collation name'

4. The result will be a list of Alter table statements for the fields that you have to change.

It may be not the way to do it with the best performance but the fastest for you.

Regards,Yelena Varsha

BU69 SSCrazy Points: 2784 More actions · Answer 3

Hi

If you do Yelenas suggestion above remember you need to drop any constraints on any columns you alter first and then put them back on afterwards.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 4

This is correct. That is why I did not compose the script myself because we don't know this specific database design.

Did you try to convert the char, varchar and text fields to Unicode to avoid the issue?

Regards,Yelena Varsha

BU69 SSCrazy Points: 2784 More actions · Answer 5

When I did it I initially did but was having problems with the text fields but the small application I found above managed to so all this for you but I was trying to do a few databases and not just a few columns so I had to find a way to do this on mass and I was going from binary to CI_AS so had to deal with all the duplicates in the tables as well.