动态更新数据库中所有字段的排序规则 [英] Update Collation of all fields in database on the fly

问题描述

我们最近将数据库从 SQL Server 2005 服务器移到了 SQL Server 2008 服务器.一切顺利，但是现在我们发现我们遇到了整理冲突.旧服务器与新服务器的排序规则不同.

We recently moved our database from our SQL Server 2005 server to our SQL Server 2008 server. Everything moved over fine, however now we are finding that we are getting collation conflicts. The old server had a different collation with the new server.

现在我们在移动之前创建的表是一个排序规则，之后创建的表是另一个排序规则.

Now our tables created before the move are one collation, and the ones created after are another collation.

有没有办法将具有旧排序规则的表/列更新为新排序规则?

Is there a way to update the tables/columns with the old collation to the new collation?

我了解设置默认数据库/服务器排序规则不会修改任何现有表 (链接).如果不需要，我真的不想重新创建数据库.

I understand setting the default database/server collation does not modify any existing tables (link). I really don't want to recreate the database if I don't have to.

非常感谢任何帮助.

更新

感谢大家的帮助，终于搞定了.

Thanks for your help guys, finally got it working.

为了将来参考，这是我的最终脚本:

For future reference, here is my final script:

SELECT 'ALTER TABLE [' + SYSOBJECTS.Name + '] ALTER COLUMN [' + SYSCOLUMNS.Name + '] ' +
SYSTYPES.name + 
    CASE systypes.NAME
    WHEN 'text' THEN ' '
    ELSE
    '(' + RTRIM(CASE SYSCOLUMNS.length
    WHEN -1 THEN 'MAX'
    ELSE CONVERT(CHAR,SYSCOLUMNS.length)
    END) + ') ' 
    END

    + ' ' + ' COLLATE Latin1_General_CI_AS ' + CASE ISNULLABLE WHEN 0 THEN 'NOT NULL' ELSE 'NULL' END
    FROM SYSCOLUMNS , SYSOBJECTS , SYSTYPES
    WHERE SYSCOLUMNS.ID = SYSOBJECTS.ID
    AND SYSOBJECTS.TYPE = 'U'
    AND SYSTYPES.Xtype = SYSCOLUMNS.xtype
    AND SYSCOLUMNS.COLLATION IS NOT NULL
    AND NOT ( sysobjects.NAME LIKE 'sys%' )
    AND NOT ( SYSTYPES.name LIKE 'sys%' )
    GO

这是包含脚本的 站点基于它.我必须对其进行调整才能使其正常工作.

Here is the site that contained the script I based it on. I had to tweak it to get it working correctly.

推荐答案

您可以使用 新 对象的排序规则="http://technet.microsoft.com/en-us/library/ms174269.aspx" rel="noreferrer">ALTER DATABASE 声明.此语句不会更改任何现有用户定义表中列的排序规则.这些可以通过使用 ALTER TABLE 的 COLLATE 子句进行更改.

You can change the collation of any new objects that are created in a user database by using the COLLATE clause of the ALTER DATABASE statement. This statement does not change the collation of the columns in any existing user-defined tables. These can be changed by using the COLLATE clause of ALTER TABLE.

参考:设置和更改数据库排序规则

如果列太多，您可以通过 SYS.COLUMNS 循环应用 ALTER TABLE 语句.

If there are too many columns, you can loop through SYS.COLUMNS to apply the ALTER TABLE statement.

这篇关于动态更新数据库中所有字段的排序规则的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！