Change collation on many tables/columns - SQL Server 2005

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?  
mrgordonzAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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
RiteshShahCommented:
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
RiteshShahCommented:
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

mrgordonzAuthor Commented:
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
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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
mrgordonzAuthor Commented:
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
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mrgordonzAuthor Commented:
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
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Welcome..
And glad to hear that you resolved it out successfully..
0
mrgordonzAuthor Commented:
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
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Yes.. you can revert here in case of any issues.
I would be watching this thread.
0
mrgordonzAuthor Commented:
Thanks - much appreciated.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.