?
Solved

Change collation on many tables/columns - SQL Server 2005

Posted on 2009-07-01
12
Medium Priority
?
1,446 Views
Last Modified: 2012-05-07
I manage a database which was created on a different server and has been restored on my server.  The server collation on my server is Latin1_General_CI_AS, but the collation of the database in question is SQL_Latin1_General_CP1_CI_AS.  The collation is causing errors in my application so I need to change the collation of the database, all the tables, and all the columns.

I have done some searching and found some examples of changing the collation of a column:

ALTER TABLE MyTable ALTER COLUMN CharCol
            varchar(10)COLLATE Latin1_General_CI_AS NOT NULL
GO

But according to Microsoft (http://msdn.microsoft.com/en-us/library/ms190920(SQL.90).aspx) there are restrictions:

You cannot change the collation of a column that is currently referenced by any one of the following:

    * A computed column
    * An index
    * Distribution statistics, either generated automatically or by the CREATE STATISTICS statement
    * A CHECK constraint
    * A FOREIGN KEY constraint

In the database I need to change there are about 1200 tables, and many thousands of columns.  Many of the columns are either indexed or foreign keys.

I thought of writing a script which has two cursors - one to select the tables, and another to select the columns of the current table - which would go through each table and column and alter the collation.  But what happens to all the columns which are indexed or foreign keys?

Can anyone suggest a reliable way to change the collation on ALL the columns, tables, and the database itself?  
0
Comment
Question by:mrgordonz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 2
12 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24760817
To change the collation of a database, you can use the statement

alter database ur_dbname collate collation_name

And to change all the columns in the tables, you need to use script below to change the collation of each and every columns.

http://sqlblogcasts.com/blogs/ssqanet/archive/2008/03/12/tsql-to-change-collation-of-database-whats-new-in-sql-2008-then.aspx

Hope this helps.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24760846
change your database collation with alter database statement provided above and for changing collation of all columns for all tables in database, follow the below link

http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1333.entry

it has two parameter, from and to. if you just want to change certain collation to something, this one is nice script.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24760858
this is also nice script for changing collation for table and columns both

http://vbnetsample.blogspot.com/2007/07/you-can-change-your-database-collation.html
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:mrgordonz
ID: 24762773
I have tried each of the suggestions, and all of them amount to the same thing: the scripts generate a bunch of ALTER TABLE ALTER COLUMN statements which change the collation on all the columns.

Unfortunately, the statements fail because almost every table has at least one indexed column, often several, and there are hundreds of foreign keys.

I gather that changing the collation involves not just executing a bunch of ALTER statements.  From one of the links provided, it appears that there are some mandatory pre-requisite tasks - dropping all indexes; remove constraints; remove computed columns; etc.  Then I can run the script to change the collation.  Then I need to re-create the indexes, constraints, etc.

If this is the only way to achieve the goal, so be it.  Have I correctly described the necessary process?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24764427
Yes.. You are correct. Missed reading it completely earlier

This is because Collation change wont work any of these scenarios which you mentioned earlier:

You cannot change the collation of a column that is currently referenced by any one of the following:

    * A computed column
    * An index
    * Distribution statistics, either generated automatically or by the CREATE STATISTICS statement
    * A CHECK constraint
    * A FOREIGN KEY constraint

Can you kindly tell me about the amount of data in your tables?

If it is less, then we can use GENERATE SCRIPTS wizard and create the scripts, Do the necessary collation changes and then insert data into the new database created using the modified scripts.

Hope this helps.
0
 

Author Comment

by:mrgordonz
ID: 24828510
Sorry for the delayed reply.

Some of the tables have only a handful of rows; some have tens of thousands of rows.  The MDF file is about 460 MB - not huge, but not small either.  I am a little nervous about playing around with the database in this manner - it is a Production database being used by a client.

How much down-time are we looking at?  Is it a risky exercise?  Is there a way to reliably drop the indexes, constraints, etc, in such a way that they can be easily (and correctly) re-created?

Are there commercial tools available which will automate the process?

Cheers,

Paul
0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 2000 total points
ID: 24833547
>> How much down-time are we looking at?  Is it a risky exercise?

Not a risky one..
Totally 10 to 15 minutes. Is that fine for you.

>> Is there a way to reliably drop the indexes, constraints, etc, in such a way that they can be easily (and correctly) re-created?

Yes. We have reliable methods.

>> Are there commercial tools available which will automate the process?

Yes...There are a few tools like Database Publishing Wizard given below. This will generate the scripts of our Entire database along with data. Once you create the scripts, change the collation of the required columns and run your scripts to create the modified database.

http://www.microsoft.com/downloads/details.aspx?FamilyId=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

Steps to do it.

1. First 5 minutes of time to backup your Production database.
2. Restore it in another machine and create the Scripts using your Database Publishing wizard.
3. Change the collation in your Required columns.
4. Run the scripts to create another database.
5. Compare this one with your production database using Comparison tools like

a. ApexSQL Diff (http://www.apexsql.com/sql_tools_diff.asp) - to compare DDL Changes.
b. ApexSQL Data Diff (http://www.apexsql.com/sql_tools_datadiff.asp) - to compare Data changes.

6. Apply any changes if you need.
7. Restore your Production database once this is completely done. ( another 5 minutes to restore)

Hope this helps.
0
 

Author Comment

by:mrgordonz
ID: 24833580
That's awesome!  Thanks for the detailed steps (and the reassurance).

I'll follow the steps and report back to you with my results.

Cheers,

Paul
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 25352196
Welcome..
And glad to hear that you resolved it out successfully..
0
 

Author Comment

by:mrgordonz
ID: 25352284
Actually - I am yet to follow your instructions - I have had lots of other stuff that climbed the priority ladder in front of this job.  I just closed this question because I kept getting reminder emails from EE to say I had an open question.

At this stage I am looking at fixing the collation stuff in about 3-4 weeks.  If I run into trouble, can I ping you for assistance?  I am happy to pay for your time.

Cheers,

Paul
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 25352736
Yes.. you can revert here in case of any issues.
I would be watching this thread.
0
 

Author Comment

by:mrgordonz
ID: 25352765
Thanks - much appreciated.
0

Featured Post

AWS Certified Solutions Architect - Associate

This course has been developed to provide you with the requisite knowledge to not only pass the AWS CSA certification exam but also gain the hands-on experience required to become a qualified AWS Solutions architect working in a real-world environment.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

801 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