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_
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?
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?
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.
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.
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
http://vbnetsample.blogspot.com/2007/07/you-can-change-your-database-collation.html
ASKER
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?
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?
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.
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.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
I'll follow the steps and report back to you with my results.
Cheers,
Paul
Welcome..
And glad to hear that you resolved it out successfully..
And glad to hear that you resolved it out successfully..
ASKER
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
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
Yes.. you can revert here in case of any issues.
I would be watching this thread.
I would be watching this thread.
ASKER
Thanks - much appreciated.
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.