Solved

Change collation on many tables/columns - SQL Server 2005

Posted on 2009-07-01
12
1,423 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
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…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

863 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

26 Experts available now in Live!

Get 1:1 Help Now