在SQL 2005中更改聚集索引列上的排序规则 [英] Change collation on clustered index column in SQL 2005

问题描述

我有一个使用不正确的排序规则创建的数据库。我更改了数据库的排序规则，但这也使各个列的排序规则都不正确。

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.

例如，如果我运行：

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

我得到一个响应：

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.

有什么办法可以解决这些问题，以更改这些列的排序规则？显然，我无法删除索引，因为它形成了主键。我想我可以暂时删除PK，但我不愿意。

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.

推荐答案

您必须删除所有依赖项。

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.

通常， MS KB 325335 包含有关如何针对整个数据库和所有列执行此操作的选项

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

这篇关于在SQL 2005中更改聚集索引列上的排序规则的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！