SQL SERVER修改排序规则——脚本篇

在上篇MS SQL 排序规则总结中，大致就数据库服务器排序规则（或者叫数据库实例排序规则）、数据库排序规则、列的排序规则粗浅的叙说了一遍，重点讲述了修改数据库服务器排序规则（数据库实例排序规则），其中对于数据库排序规则的修改只是粗略带过。其实相对而言，修改服务器排序规则（数据库实例排序规则）相对简单一些，修改数据库的排序规则就复杂多了，因为涉及到数据、SQL脚本等等，例如，一不小心，修改排序规则后，数据当中可能就会出现乱码； 另外，修改数据库排序规则麻烦的是要大量修改相关表的字段的排序规则，如果不用脚本批量处理，那么这项工作想想就让人望而生畏。做这项工作前，一定要做好备份或在测试服务器测试通过后，然后进行数据库排序规则修改。

如果要首先了解一下修改排序规则，首先看看MS SQL 排序规则总结当中的介绍，重复的内容就不做过多介绍了。我们首先来看看，修改排序规则当中会遇到哪些问题吧。

DBMonitor数据库的排序规则为 Chinese_PRC_CI_AS，在数据库中创建TEST表，插入数据后，修改其排序规则为SQL_Latin1_General_CP1_CI_AS，然后

USE DBMonitor;

GO

CREATE TABLE TEST

(

 ID INT ,

 NAME VARCHAR(12),

 CITY NVARCHAR(12)

)

CREATE INDEX IDX_TEST_NAME ON TEST(NAME);

CREATE INDEX IDX_TEST_CITY ON TEST(CITY);

INSERT INTO TEST

…..

ALTER DATABASE DBMonitor COLLATE SQL_Latin1_General_CP1_CI_AS

修改排序规则后，你会发现数据库当中，修改排序规则前新建的表，其列的排序规则依然是旧的排序规则，当然，有时候它不会有任何影响，但是有时候也会导致SQL脚本中出现排序规则冲突等错误。

SELECT object_id,name, collation_name FROM sys.columns WHERE object_id =OBJECT_ID('TEST')

如上所示，修改列的排序规则当中，如果在这个字段上建有索引，那么修改列的排序规则时，就会报上面错误信息。这时需要先删除索引，修改列的排序规则后，然后重建索引。

所以要彻底修改这些列的排序规则，这项工作相当的繁琐和郁闷，还是推荐大家看看这位兄台的Easy way to change collation of all database objects in SQL Server的博客，由于这篇博客里面有些脚本没有写全，有些脚本我稍作了修改，例如将生成创建表索引、约束、删除表相关索引、约束的脚本写入表里面。Fix了一些小bug，至于还有没有其它bug，暂时还没有发现，如果大家有发现其它bug，欢迎指出错误。

SQL Script :ScriptDropTableKeys 创建生成指定表的约束、索引的脚本；

--USE [DatabaseName]
--GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


IF EXISTS (SELECT 1 FROM sysobjects WHERE id=OBJECT_ID(N'ScriptCreateTableKeys') AND OBJECTPROPERTY(id, 'IsProcedure') =1)
    DROP PROCEDURE ScriptCreateTableKeys;
GO

--================================================================================================================
--        ProcedureName        :            ScriptCreateTableKeys
--        Author                :            Raymund Macaalay
--        CreateDate            :            2011-09-11
--        Description            :            生成数据库里指定表的Constraints,Primary Key, Foreign Key, Index的创建脚本.
/*****************************************************************************************************************
        Parameters            :                                    参数说明
******************************************************************************************************************
        @table_name            :                    数据库用户表的名字
******************************************************************************************************************
   Modified Date    Modified User     Version                 Modified Reason
******************************************************************************************************************
    2013-11-06             Kerry         V01.00.00         修改生成脚本的输出方式，将其写入表CreateTableKeys
    2013-11-08             Kerry       V01.00.01       Fix生成索引的一些bugs：
                                                        1: 非唯一索引不生成索引
                                                        2：索引type_des为HEAP的索引也会生成。
******************************************************************************************************************/

--================================================================================================================


CREATE PROC [dbo].[ScriptCreateTableKeys]
    @table_name SYSNAME
AS
BEGIN
    SET NOCOUNT ON

    --Note: Disabled keys and constraints are ignored
    --TODO: Drop and re-create referencing XML indexes, FTS catalogs

    DECLARE @crlf CHAR(2)
    SET @crlf = CHAR(13) + CHAR(10)
    DECLARE @version CHAR(4)
    SET @version = SUBSTRING(@@VERSION, LEN('Microsoft SQL Server') + 2, 4)
    DECLARE @object_id INT
    SET @object_id = OBJECT_ID(@table_name)
    DECLARE @sql NVARCHAR(MAX)

    IF @version NOT IN ('2005', '2008')
    BEGIN
        RAISERROR('This script only supports SQL Server 2005 and 2008', 16, 1)
        RETURN
    END

    SET @sql = '' +
        'SELECT ' +
            'CASE ' +
                'WHEN 1 IN (i.is_primary_key, i.is_unique_constraint) THEN ' +
                    '''ALTER TABLE '' + ' +
                        'QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) + ''.'' + ' +
                        'QUOTENAME(OBJECT_NAME(i.object_id)) + @crlf + ' +
                    '''ADD '' + ' +
                        'CASE k.is_system_named ' +
                            'WHEN 0 THEN ''CONSTRAINT '' + QUOTENAME(k.name) + @crlf ' +
                            'ELSE '''' ' +
                        'END + ' +
                    'CASE k.type ' +
                        'WHEN ''UQ'' THEN ''UNIQUE'' ' +
                        'ELSE ''PRIMARY KEY'' ' +
                    'END + '' '' + ' +
                    'i.type_desc  + @crlf + ' +
                    'kc.key_columns + @crlf ' +
                'ELSE ' +
                    '''CREATE '' + CASE WHEN i.is_unique = 1 THEN '' UNIQUE '' ELSE '''' end + i.type_desc + '' INDEX '' + ' +
                        'QUOTENAME(i.name) + @crlf + ' +
                    '''ON '' + ' +
                        'QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) + ''.'' + ' +
                        'QUOTENAME(OBJECT_NAME(i.object_id)) + @crlf + ' +
                    'kc.key_columns + @crlf + ' +
                    'COALESCE ' +
                    '( ' +
                        '''INCLUDE '' + @crlf + ' +
                        '''( '' + @crlf + ' +
                            'STUFF ' +
                            '( ' +
                                '( ' +
                                    'SELECT ' +
                                    '( ' +
                                        'SELECT ' +
                                            ''','' + @crlf + '' '' + QUOTENAME(c.name) AS [text()] ' +
                                        'FROM sys.index_columns AS ic ' +
                                        'JOIN sys.columns AS c ON ' +
                                            'c.object_id = ic.object_id ' +
                                            'AND c.column_id = ic.column_id ' +
                                        'WHERE ' +
                                            'ic.object_id = i.object_id ' +
                                            'AND ic.index_id = i.index_id ' +
                                            'AND ic.is_included_column = 1 ' +
                                        'ORDER BY ' +
                                            'ic.key_ordinal ' +
                                        'FOR XML PATH(''''), TYPE ' +
                                    ').value(''.'', ''VARCHAR(MAX)'') ' +
                                '), ' +
                                '1, ' +
                                '3, ' +
                                ''''' ' +
                            ') + @crlf + ' +
                        ''')'' + @crlf, ' +
                        ''''' ' +
                    ') ' +
            'END + ' +
            '''WITH '' + @crlf + ' +
            '''('' + @crlf + ' +
                ''' PAD_INDEX = '' + ' +
                        'CASE CONVERT(VARCHAR, i.is_padded) ' +
                            'WHEN 1 THEN ''ON'' ' +
                            'ELSE ''OFF'' ' +
                        'END + '','' + @crlf + ' +
                'CASE i.fill_factor ' +
                    'WHEN 0 THEN '''' ' +
                    'ELSE ' +
                        ''' FILLFACTOR = '' + ' +
                                'CONVERT(VARCHAR, i.fill_factor) + '','' + @crlf ' +
                'END + ' +
                ''' IGNORE_DUP_KEY = '' + ' +
                        'CASE CONVERT(VARCHAR, i.ignore_dup_key) ' +
                            'WHEN 1 THEN ''ON'' ' +
                            'ELSE ''OFF'' ' +
                        'END + '','' + @crlf + ' +
                ''' ALLOW_ROW_LOCKS = '' + ' +
                        'CASE CONVERT(VARCHAR, i.allow_row_locks) ' +
                            'WHEN 1 THEN ''ON'' ' +
                            'ELSE ''OFF'' ' +
                        'END + '','' + @crlf + ' +
                ''' ALLOW_PAGE_LOCKS = '' + ' +
                        'CASE CONVERT(VARCHAR, i.allow_page_locks) ' +
                            'WHEN 1 THEN ''ON'' ' +
                            'ELSE ''OFF'' ' +
                        'END + ' +
                CASE @version
                    WHEN '2005' THEN ''
                    ELSE
                        ''','' + @crlf + ' +
                        ''' DATA_COMPRESSION = '' + ' +
                            '( ' +
                                'SELECT ' +
                                    'CASE ' +
                                        'WHEN MIN(p.data_compression_desc) =
                                          MAX(p.data_compression_desc)
                                          THEN MAX(p.data_compression_desc) ' +
                                          'ELSE ''[PARTITIONS USE
                                          MULTIPLE COMPRESSION TYPES]'' ' +
                                    'END ' +
                                'FROM sys.partitions AS p ' +
                                'WHERE ' +
                                    'p.object_id = i.object_id ' +
                                    'AND p.index_id = i.index_id ' +
                            ') '
                END + '+ @crlf + ' +
            ''') '' + @crlf + ' +
            '''ON '' + ds.data_space + '';'' + ' +
                '@crlf + @crlf COLLATE database_default AS [-- Create Candidate Keys] ' +
        'FROM sys.indexes AS i ' +
        'LEFT OUTER JOIN sys.key_constraints AS k ON ' +
            'k.parent_object_id = i.object_id ' +
            'AND k.unique_index_id = i.index_id ' +
        'CROSS APPLY ' +
        '( ' +
            'SELECT ' +
                '''( '' + @crlf + ' +
                    'STUFF ' +
                    '( ' +
                        '( ' +
                            'SELECT ' +
                            '( ' +
                                'SELECT ' +
                                    ''','' + @crlf + '' '' + QUOTENAME(c.name) AS [text()] ' +
                                'FROM sys.index_columns AS ic ' +
                                'JOIN sys.columns AS c ON ' +
                                    'c.object_id = ic.object_id ' +
                                    'AND c.column_id = ic.column_id ' +
                                'WHERE ' +
                                    'ic.object_id = i.object_id ' +
                                    'AND ic.index_id = i.index_id ' +
                                    'AND ic.key_ordinal > 0 ' +
                                'ORDER BY ' +
                                    'ic.key_ordinal ' +
                                'FOR XML PATH(''''), TYPE ' +
                            ').value(''.'', ''VARCHAR(MAX)'') ' +
                        '), ' +
                        '1, ' +
                        '3, ' +
                        ''''' ' +
                    ') + @crlf + ' +
                ''')'' ' +
        ') AS kc (key_columns) ' +
        'CROSS APPLY ' +
        '( ' +
            'SELECT ' +
                'QUOTENAME(d.name) + ' +
                    'CASE d.type ' +
                        'WHEN ''PS'' THEN ' +
                            '+ ' +
                            '''('' + ' +
                                '( ' +
                                    'SELECT ' +
                                        'QUOTENAME(c.name) ' +
                                    'FROM sys.index_columns AS ic ' +
                                    'JOIN sys.columns AS c ON ' +
                                        'c.object_id = ic.object_id ' +
                                        'AND c.column_id = ic.column_id ' +
                                    'WHERE ' +
                                        'ic.object_id = i.object_id ' +
                                        'AND ic.index_id = i.index_id ' +
                                        'AND ic.partition_ordinal = 1 ' +
                                ') + ' +
                            ''')'' ' +
                        'ELSE '''' ' +
                    'END ' +
            'FROM sys.data_spaces AS d ' +
            'WHERE ' +
                'd.data_space_id = i.data_space_id ' +
        ') AS ds (data_space) ' +
        'WHERE ' +
            'i.object_id = @object_id ' +
            --'AND i.is_unique = 1 ' +
            'AND i.type >=1' +
            --filtered and hypothetical indexes cannot be candidate keys
            CASE @version
                WHEN '2008' THEN 'AND i.has_filter = 0 '
                ELSE ''
            END +
            'AND i.is_hypothetical = 0 ' +
            'AND i.is_disabled = 0 ' +
        'ORDER BY ' +
            'i.index_id '

    --print @sql;
    INSERT INTO  CreateTableKeys
    EXEC sp_executesql @sql,  N'@object_id INT, @crlf CHAR(2)',
        @object_id, @crlf

    INSERT INTO  CreateTableKeys
    SELECT
        'ALTER TABLE ' +
            QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id)) + '.' +
            QUOTENAME(OBJECT_NAME(fk.parent_object_id)) + @crlf +
        CASE fk.is_not_trusted
            WHEN 0 THEN 'WITH CHECK '
            ELSE 'WITH NOCHECK '
        END +
            'ADD ' +
                CASE fk.is_system_named
                    WHEN 0 THEN 'CONSTRAINT ' + QUOTENAME(name) + @crlf
                    ELSE ''
                END +
        'FOREIGN KEY ' + @crlf +
        '( ' + @crlf +
            STUFF
(
(
                    SELECT
(
                        SELECT
                            ',' + @crlf + ' ' + QUOTENAME(c.name) AS [text()]
                        FROM sys.foreign_key_columns AS fc
                        JOIN sys.columns AS c ON
                            c.object_id = fc.parent_object_id
                            AND c.column_id = fc.parent_column_id
                        WHERE
                            fc.constraint_object_id = fk.object_id
                        ORDER BY
                            fc.constraint_column_id
                        FOR XML PATH(''), TYPE
                    ).value('.', 'VARCHAR(MAX)')
                ),
                1,
                3,
                ''
            ) + @crlf +
        ') ' +
        'REFERENCES ' +
            QUOTENAME(OBJECT_SCHEMA_NAME(fk.referenced_object_id)) + '.' +
            QUOTENAME(OBJECT_NAME(fk.referenced_object_id)) + @crlf +
        '( ' + @crlf +
            STUFF
(
(
                    SELECT
(
                        SELECT
                            ',' + @crlf + ' ' + QUOTENAME(c.name) AS [text()]
                        FROM sys.foreign_key_columns AS fc
                        JOIN sys.columns AS c ON
                            c.object_id = fc.referenced_object_id
                            AND c.column_id = fc.referenced_column_id
                        WHERE
                            fc.constraint_object_id = fk.object_id
                        ORDER BY
                            fc.constraint_column_id
                        FOR XML PATH(''), TYPE
                    ).value('.', 'VARCHAR(MAX)')
                ),
                1,
                3,
                ''
            ) + @crlf +
        ');
        GO' +
            @crlf + @crlf COLLATE database_default AS [-- Create Referencing FKs]
    FROM sys.foreign_keys AS fk
    WHERE
        referenced_object_id = @object_id
        AND is_disabled = 0
    ORDER BY
        key_index_id

END

GO

SQL Script：ScriptDropTableKeys 创建删除指定表的约束、索引的脚本

--USE [DatabaseName]
--GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT 1 FROM sysobjects WHERE id=OBJECT_ID(N'ScriptDropTableKeys') AND OBJECTPROPERTY(id, 'IsProcedure') =1)
    DROP PROCEDURE ScriptDropTableKeys;
GO

--===============================================================================================================
--        ProcedureName        :            ScriptDropTableKeys
--        Author                :            Raymund Macaalay
--        CreateDate            :            2011-09-11
--        Description            :            删除数据库里指定表的Constraints,Primary Key, Foreign Key, Index
/*****************************************************************************************************************
        Parameters            :                                    参数说明
******************************************************************************************************************
        @table_name            :                    数据库用户表的名字
******************************************************************************************************************
   Modified Date    Modified User     Version                 Modified Reason
******************************************************************************************************************
    2013-11-06             Kerry         V01.00.00         修改生成脚本的输出方式，将其写入表DropTableKeys
    2013-12-08             Kerry         V01.00.00         Fix掉脚本中一个小bug: 不生成删除非唯一索引的SQL Script
*****************************************************************************************************************/

--==============================================================================================================

CREATE PROC [dbo].[ScriptDropTableKeys]
    @table_name SYSNAME
AS
BEGIN
    SET NOCOUNT ON

    --Note: Disabled keys and constraints are ignored
    --TODO: Drop and re-create referencing XML indexes, FTS catalogs

    DECLARE @crlf CHAR(2)
    SET @crlf = CHAR(13) + CHAR(10)
    DECLARE @version CHAR(4)
    SET @version = SUBSTRING(@@VERSION, LEN('Microsoft SQL Server') + 2, 4)
    DECLARE @object_id INT
    SET @object_id = OBJECT_ID(@table_name)
    DECLARE @sql NVARCHAR(MAX)

    IF @version NOT IN ('2005', '2008')
    BEGIN
        RAISERROR('This script only supports SQL Server 2005 and 2008', 16, 1)
        RETURN
    END

    INSERT INTO dbo.DropTableKeys
    SELECT
        'ALTER TABLE ' +
            QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' +
            QUOTENAME(OBJECT_NAME(parent_object_id)) + @crlf +
        'DROP CONSTRAINT ' + QUOTENAME(name) + ';' +
            @crlf + @crlf COLLATE database_default AS [-- Drop Referencing FKs]
    FROM sys.foreign_keys
    WHERE
        referenced_object_id = @object_id
        AND is_disabled = 0
    ORDER BY
        key_index_id DESC


    SET @sql = '' +
        'SELECT ' +
            'statement AS [-- Drop Candidate Keys] ' +
        'FROM ' +
        '( ' +
            'SELECT ' +
                'CASE ' +
                    'WHEN 1 IN (i.is_unique_constraint, i.is_primary_key) THEN ' +
                        '''ALTER TABLE '' + ' +
                            'QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) + ''.'' + ' +
                            'QUOTENAME(OBJECT_NAME(i.object_id)) + @crlf + ' +
                        '''DROP CONSTRAINT '' + QUOTENAME(i.name) + '';'' + ' +
                            '@crlf + @crlf COLLATE database_default ' +
                    'ELSE ' +
                        '''DROP INDEX '' + QUOTENAME(i.name) + @crlf + ' +
                        '''ON '' + ' +
                            'QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + ''.'' + ' +
                            'QUOTENAME(OBJECT_NAME(object_id)) + '';'' + ' +
                                '@crlf + @crlf COLLATE database_default ' +
                'END AS statement, ' +
                'i.index_id ' +
            'FROM sys.indexes AS i ' +
            'WHERE ' +
                'i.object_id = @object_id ' +
                --'AND i.is_unique = 1 ' +
                ' AND i.type >=1' +
                --filtered and hypothetical indexes cannot be candidate keys
                CASE @version
                    WHEN '2008' THEN 'AND i.has_filter = 0 '
                    ELSE ''
                END +
                'AND i.is_hypothetical = 0 ' +
                'AND i.is_disabled = 0 ' +
        ') AS x ' +
        'ORDER BY ' +
            'index_id DESC;'

    --PRINT @sql;
    INSERT INTO  dbo.DropTableKeys
    EXEC sp_executesql @sql,
        N'@object_id INT, @crlf CHAR(2)',
        @object_id, @crlf

END
GO

SQL Script: sp_change_collation_script 创建修改列排序规则的脚本，以及循环调用ScriptDropTableKeys 、ScriptDropTableKeys 生成对应的脚本

--USE [DW_ESQUEL]
--GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT 1 FROM sysobjects WHERE id=OBJECT_ID(N'sp_change_collation_script') AND OBJECTPROPERTY(id, 'IsProcedure') =1)
    DROP PROCEDURE sp_change_collation_script;
GO

--===============================================================================================
--        ProcedureName        :            sp_change_collation_script
--        Author                :            Kerry
--        CreateDate            :            2013-11-6
--        Description            :            组合、补全Raymund Macaalay的脚本，生成改变列排序规则的脚本
/*************************************************************************************************
        Parameters            :                                    参数说明
**************************************************************************************************
        @table_name            :                    数据库用户表的名字
**************************************************************************************************
   Modified Date    Modified User     Version                 Modified Reason
**************************************************************************************************
    2013-11-6             Kerry         V01.00.00
*************************************************************************************************/

--===============================================================================================
CREATE PROCEDURE [dbo].[sp_change_collation_script]
        @CollationName SYSNAME
AS
BEGIN

SET NOCOUNT ON
DECLARE @SQLText            VARCHAR(MAX) ;
DECLARE @TableName            NVARCHAR(255);
DECLARE @ColumnName            sysname         ;
DECLARE @DataType            NVARCHAR(128);
DECLARE @CharacterMaxLen    INT             ;
DECLARE @IsNullable            VARCHAR(3)     ;
DECLARE @CreateSqlRowNum    INT;
DECLARE @DropSqlRowNum        INT;

DECLARE MyTableCursor        Cursor
FOR
SELECT name FROM sys.tables WHERE [type] = 'U' and name <> 'sysdiagrams' ORDER BY name


IF NOT EXISTS ( SELECT  1
                FROM    dbo.sysobjects
                WHERE   id = OBJECT_ID(N'[dbo].[ChangeColCollation]')
                        AND xtype = 'U' )
    BEGIN

        CREATE TABLE [dbo].[ChangeColCollation] ( SQL_TEXT VARCHAR(MAX) )
    END
ELSE
    TRUNCATE TABLE [dbo].[ChangeColCollation];


OPEN MyTableCursor;
FETCH NEXT FROM MyTableCursor INTO @TableName


WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE MyColumnCursor Cursor
        FOR
        SELECT COLUMN_NAME,DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
            IS_NULLABLE from information_schema.columns
            WHERE table_name = @TableName AND  (Data_Type LIKE '%char%'
            OR Data_Type LIKE '%text%') AND COLLATION_NAME <> @CollationName
            ORDER BY ordinal_position
        Open MyColumnCursor

        FETCH NEXT FROM MyColumnCursor INTO @ColumnName, @DataType,
              @CharacterMaxLen, @IsNullable
        WHILE @@FETCH_STATUS = 0
            BEGIN
            SET @SQLText = 'ALTER TABLE ' + @TableName + ' ALTER COLUMN [' + @ColumnName + '] ' +
              @DataType + '(' + CASE WHEN @CharacterMaxLen = -1 THEN 'MAX' ELSE CAST(@CharacterMaxLen AS VARCHAR(6)) END +
              ') COLLATE ' + @CollationName + ' ' +
              CASE WHEN @IsNullable = 'NO' THEN 'NOT NULL' ELSE 'NULL' END
            --PRINT @SQLText

            INSERT INTO ChangeColCollation
            VALUES (@SQLText);

        FETCH NEXT FROM MyColumnCursor INTO @ColumnName, @DataType,
              @CharacterMaxLen, @IsNullable
        END
        CLOSE MyColumnCursor
        DEALLOCATE MyColumnCursor

FETCH NEXT FROM MyTableCursor INTO @TableName
END
CLOSE MyTableCursor
--DEALLOCATE MyTableCursor


IF NOT EXISTS ( SELECT  1
                FROM    dbo.sysobjects
                WHERE   id = OBJECT_ID(N'[dbo].[CreateTableKeys]')
                        AND xtype = 'U' )
    BEGIN

        CREATE TABLE [dbo].[CreateTableKeys] ( SQL_TEXT VARCHAR(MAX) )
    END
ELSE
    TRUNCATE TABLE [dbo].[CreateTableKeys];



IF NOT EXISTS ( SELECT  1
                FROM    dbo.sysobjects
                WHERE   id = OBJECT_ID(N'[dbo].[DropTableKeys]')
                        AND XTYPE = 'U' )
    BEGIN
        CREATE TABLE dbo.DropTableKeys ( SQL_TEXT VARCHAR(MAX) )
    END
ELSE
    TRUNCATE TABLE dbo.DropTableKeys;



OPEN MyTableCursor

FETCH NEXT FROM MyTableCursor INTO @TableName
PRINT @TableName
WHILE @@FETCH_STATUS = 0
    BEGIN

     EXEC ScriptCreateTableKeys @TableName  --生成创建约束、索引等的脚本
     EXEC ScriptDropTableKeys @TableName     --生成删除约束、索引等的脚本
    FETCH NEXT FROM MyTableCursor INTO @TableName
END
CLOSE MyTableCursor
DEALLOCATE MyTableCursor


SELECT @CreateSqlRowNum = COUNT(1) FROM dbo.CreateTableKeys;
SELECT @DropSqlRowNum = COUNT(1) FROM dbo.DropTableKeys;

IF @CreateSqlRowNum != @DropSqlRowNum
    PRINT 'The table CreateTableKeys rows is different from the row of DropTableKeys ,please check the reason'


END
GO

修改数据库的排序规则时，按如下步骤顺序执行SQL

ALTER DATABASE DataBase COLLATE Chinese_PRC_CI_AS

EXEC  sp_change_collation_script 'Chinese_PRC_CI_AS';

--执行下表里面的SQL语句
SELECT * FROM dbo.DropTableKeys

--执行下表里面的SQL语句
SELECT * FROM ChangeColCollation

--执行下表里面的SQL语句
SELECT * FROM dbo.CreateTableKeys

 

最后验证没有问题后，可以删除dbo.CreateTableKeys、dbo.DropTableKeys、dbo.ChangeColCollation等表。修改数据库的排序规则完成。