Change collation on clustered index column in SQL 2005

Question

I have a Database that was created with an incorrect collation. I changed the collation of the database, but this left the individual columns with the incorrect collation also. This causes me a problem.

So, I wrote a script to loop through and change the collation of the individual columns and this basically worked, except for a few columns that are part of a clustered index on their respective tables. These I cannot change.

For example if I run:

ALTER TABLE MyTable 
ALTER COLUMN MyColumn varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

I get a response of:

Msg 5074, Level 16, State 1, Line 1
The object 'DF_MyTable_MyColumn' is dependent on column 'MyColumn'.
Msg 5074, Level 16, State 1, Line 1
The object 'PK_MyTable_MyColumn_MyOtherColumn' is dependent on column 'MyColumn'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN deleted failed because one or more objects access this column.

Is there any way to work around this to change the collation of these columns? I cannot drop the index, obviously, as it forms the Primary Key. I suppose I could remove the PK temporarily but I'd rather not.

Answer 1

You have to remove all dependencies.

When you change the DB collation, you only change the system tables. All other text-type columns need changed manually.

Generally, MS KB 325335 has options on how to do this for the whole db and all columns