Solved

Change collation on many tables/columns - SQL Server 2005

Posted on 2009-07-01
12
1,419 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
  • 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
 

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now