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

时间:2022-01-17 00:53:26

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

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

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

   1: USE DBMonitor; 
   2:  
   3: GO 
   4:  
   5: CREATE TABLE TEST 
   6:  
   7: ( 
   8:  
   9:  ID INT , 
  10:  
  11:  NAME VARCHAR(12), 
  12:  
  13:  CITY NVARCHAR(12) 
  14:  
  15: ) 
  16:  
  17: CREATE INDEX IDX_TEST_NAME ON TEST(NAME); 
  18:  
  19: CREATE INDEX IDX_TEST_CITY ON TEST(CITY);
  20:  
  21: INSERT INTO TEST 
  22:  
  23: …..
  24:  
  25: ALTER DATABASE DBMonitor COLLATE SQL_Latin1_General_CP1_CI_AS 
  26:  

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

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

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

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

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

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

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

   1: --USE [DatabaseName]
   2: --GO
   3:  
   4: SET ANSI_NULLS ON
   5: GO
   6:  
   7: SET QUOTED_IDENTIFIER ON
   8: GO
   9:  
  10:  
  11: IF EXISTS (SELECT 1 FROM sysobjects WHERE id=OBJECT_ID(N'ScriptCreateTableKeys') AND OBJECTPROPERTY(id, 'IsProcedure') =1)
  12:     DROP PROCEDURE ScriptCreateTableKeys;
  13: GO
  14:  
  15: --================================================================================================================
  16: --        ProcedureName        :            ScriptCreateTableKeys
  17: --        Author                :            Raymund Macaalay    
  18: --        CreateDate            :            2011-09-11
  19: --        Description            :            生成数据库里指定表的Constraints,Primary Key, Foreign Key, Index的创建脚本. 
  20: /*****************************************************************************************************************
  21:         Parameters            :                                    参数说明
  22: ******************************************************************************************************************
  23:         @table_name            :                    数据库用户表的名字
  24: ******************************************************************************************************************
  25:    Modified Date    Modified User     Version                 Modified Reason
  26: ******************************************************************************************************************
  27:     2013-11-06             Kerry         V01.00.00         修改生成脚本的输出方式,将其写入表CreateTableKeys
  28:     2013-11-08             Kerry       V01.00.01       Fix生成索引的一些bugs:
  29:                                                         1: 非唯一索引不生成索引
  30:                                                         2:索引type_des为HEAP的索引也会生成。                                                                           
  31: ******************************************************************************************************************/
  32:  
  33: --================================================================================================================
  34:  
  35:  
  36: CREATE PROC [dbo].[ScriptCreateTableKeys]
  37:     @table_name SYSNAME
  38: AS
  39: BEGIN
  40:     SET NOCOUNT ON
  41:  
  42:     --Note: Disabled keys and constraints are ignored
  43:     --TODO: Drop and re-create referencing XML indexes, FTS catalogs
  44:  
  45:     DECLARE @crlf CHAR(2)
  46:     SET @crlf = CHAR(13) + CHAR(10)
  47:     DECLARE @version CHAR(4)
  48:     SET @version = SUBSTRING(@@VERSION, LEN('Microsoft SQL Server') + 2, 4)
  49:     DECLARE @object_id INT
  50:     SET @object_id = OBJECT_ID(@table_name)
  51:     DECLARE @sql NVARCHAR(MAX)
  52:  
  53:     IF @version NOT IN ('2005', '2008')
  54:     BEGIN
  55:         RAISERROR('This script only supports SQL Server 2005 and 2008', 16, 1)
  56:         RETURN
  57:     END
  58:  
  59:     SET @sql = '' +
  60:         'SELECT ' +
  61:             'CASE ' +
  62:                 'WHEN 1 IN (i.is_primary_key, i.is_unique_constraint) THEN ' +
  63:                     '''ALTER TABLE '' + ' +
  64:                         'QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) + ''.'' + ' +
  65:                         'QUOTENAME(OBJECT_NAME(i.object_id)) + @crlf + ' +
  66:                     '''ADD '' + ' +
  67:                         'CASE k.is_system_named ' +
  68:                             'WHEN 0 THEN ''CONSTRAINT '' + QUOTENAME(k.name) + @crlf ' +
  69:                             'ELSE '''' ' +
  70:                         'END + ' +
  71:                     'CASE k.type ' +
  72:                         'WHEN ''UQ'' THEN ''UNIQUE'' ' +
  73:                         'ELSE ''PRIMARY KEY'' ' +
  74:                     'END + '' '' + ' +
  75:                     'i.type_desc  + @crlf + ' +
  76:                     'kc.key_columns + @crlf ' +
  77:                 'ELSE ' +
  78:                     '''CREATE '' + CASE WHEN i.is_unique = 1 THEN '' UNIQUE '' ELSE '''' end + i.type_desc + '' INDEX '' + ' +
  79:                         'QUOTENAME(i.name) + @crlf + ' +
  80:                     '''ON '' + ' +
  81:                         'QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) + ''.'' + ' +
  82:                         'QUOTENAME(OBJECT_NAME(i.object_id)) + @crlf + ' +
  83:                     'kc.key_columns + @crlf + ' +
  84:                     'COALESCE ' +
  85:                     '( ' +
  86:                         '''INCLUDE '' + @crlf + ' +
  87:                         '''( '' + @crlf + ' +
  88:                             'STUFF ' +
  89:                             '( ' +
  90:                                 '( ' +
  91:                                     'SELECT ' +
  92:                                     '( ' +
  93:                                         'SELECT ' +
  94:                                             ''','' + @crlf + '' '' + QUOTENAME(c.name) AS [text()] ' +
  95:                                         'FROM sys.index_columns AS ic ' +
  96:                                         'JOIN sys.columns AS c ON ' +
  97:                                             'c.object_id = ic.object_id ' +
  98:                                             'AND c.column_id = ic.column_id ' +
  99:                                         'WHERE ' +
 100:                                             'ic.object_id = i.object_id ' +
 101:                                             'AND ic.index_id = i.index_id ' +
 102:                                             'AND ic.is_included_column = 1 ' +
 103:                                         'ORDER BY ' +
 104:                                             'ic.key_ordinal ' +
 105:                                         'FOR XML PATH(''''), TYPE ' +
 106:                                     ').value(''.'', ''VARCHAR(MAX)'') ' +
 107:                                 '), ' +
 108:                                 '1, ' +
 109:                                 '3, ' +
 110:                                 ''''' ' +
 111:                             ') + @crlf + ' +
 112:                         ''')'' + @crlf, ' +
 113:                         ''''' ' +
 114:                     ') ' +
 115:             'END + ' +
 116:             '''WITH '' + @crlf + ' +
 117:             '''('' + @crlf + ' +
 118:                 ''' PAD_INDEX = '' + ' +
 119:                         'CASE CONVERT(VARCHAR, i.is_padded) ' +
 120:                             'WHEN 1 THEN ''ON'' ' +
 121:                             'ELSE ''OFF'' ' +
 122:                         'END + '','' + @crlf + ' +
 123:                 'CASE i.fill_factor ' +
 124:                     'WHEN 0 THEN '''' ' +
 125:                     'ELSE ' +
 126:                         ''' FILLFACTOR = '' + ' +
 127:                                 'CONVERT(VARCHAR, i.fill_factor) + '','' + @crlf ' +
 128:                 'END + ' +
 129:                 ''' IGNORE_DUP_KEY = '' + ' +
 130:                         'CASE CONVERT(VARCHAR, i.ignore_dup_key) ' +
 131:                             'WHEN 1 THEN ''ON'' ' +
 132:                             'ELSE ''OFF'' ' +
 133:                         'END + '','' + @crlf + ' +
 134:                 ''' ALLOW_ROW_LOCKS = '' + ' +
 135:                         'CASE CONVERT(VARCHAR, i.allow_row_locks) ' +
 136:                             'WHEN 1 THEN ''ON'' ' +
 137:                             'ELSE ''OFF'' ' +
 138:                         'END + '','' + @crlf + ' +
 139:                 ''' ALLOW_PAGE_LOCKS = '' + ' +
 140:                         'CASE CONVERT(VARCHAR, i.allow_page_locks) ' +
 141:                             'WHEN 1 THEN ''ON'' ' +
 142:                             'ELSE ''OFF'' ' +
 143:                         'END + ' +
 144:                 CASE @version
 145:                     WHEN '2005' THEN ''
 146:                     ELSE             
 147:                         ''','' + @crlf + ' +
 148:                         ''' DATA_COMPRESSION = '' + ' +
 149:                             '( ' +
 150:                                 'SELECT ' +
 151:                                     'CASE ' +
 152:                                         'WHEN MIN(p.data_compression_desc) = 
 153:                                           MAX(p.data_compression_desc) 
 154:                                           THEN MAX(p.data_compression_desc) ' +
 155:                                           'ELSE ''[PARTITIONS USE 
 156:                                           MULTIPLE COMPRESSION TYPES]'' ' +
 157:                                     'END ' +
 158:                                 'FROM sys.partitions AS p ' +
 159:                                 'WHERE ' +
 160:                                     'p.object_id = i.object_id ' +
 161:                                     'AND p.index_id = i.index_id ' +
 162:                             ') '
 163:                 END + '+ @crlf + ' +
 164:             ''') '' + @crlf + ' +
 165:             '''ON '' + ds.data_space + '';'' + ' +
 166:                 '@crlf + @crlf COLLATE database_default AS [-- Create Candidate Keys] ' +
 167:         'FROM sys.indexes AS i ' +
 168:         'LEFT OUTER JOIN sys.key_constraints AS k ON ' +
 169:             'k.parent_object_id = i.object_id ' +
 170:             'AND k.unique_index_id = i.index_id ' +
 171:         'CROSS APPLY ' +
 172:         '( ' +
 173:             'SELECT ' +
 174:                 '''( '' + @crlf + ' +
 175:                     'STUFF ' +
 176:                     '( ' +
 177:                         '( ' +
 178:                             'SELECT ' +
 179:                             '( ' +
 180:                                 'SELECT ' +
 181:                                     ''','' + @crlf + '' '' + QUOTENAME(c.name) AS [text()] ' +
 182:                                 'FROM sys.index_columns AS ic ' +
 183:                                 'JOIN sys.columns AS c ON ' +
 184:                                     'c.object_id = ic.object_id ' +
 185:                                     'AND c.column_id = ic.column_id ' +
 186:                                 'WHERE ' +
 187:                                     'ic.object_id = i.object_id ' +
 188:                                     'AND ic.index_id = i.index_id ' +
 189:                                     'AND ic.key_ordinal > 0 ' +
 190:                                 'ORDER BY ' +
 191:                                     'ic.key_ordinal ' +
 192:                                 'FOR XML PATH(''''), TYPE ' +
 193:                             ').value(''.'', ''VARCHAR(MAX)'') ' +
 194:                         '), ' +
 195:                         '1, ' +
 196:                         '3, ' +
 197:                         ''''' ' +
 198:                     ') + @crlf + ' +
 199:                 ''')'' ' +
 200:         ') AS kc (key_columns) ' +
 201:         'CROSS APPLY ' +
 202:         '( ' +
 203:             'SELECT ' +
 204:                 'QUOTENAME(d.name) + ' +
 205:                     'CASE d.type ' +
 206:                         'WHEN ''PS'' THEN ' +
 207:                             '+ ' +
 208:                             '''('' + ' +
 209:                                 '( ' +
 210:                                     'SELECT ' +
 211:                                         'QUOTENAME(c.name) ' +
 212:                                     'FROM sys.index_columns AS ic ' +
 213:                                     'JOIN sys.columns AS c ON ' +
 214:                                         'c.object_id = ic.object_id ' +
 215:                                         'AND c.column_id = ic.column_id ' +
 216:                                     'WHERE ' +
 217:                                         'ic.object_id = i.object_id ' +
 218:                                         'AND ic.index_id = i.index_id ' +
 219:                                         'AND ic.partition_ordinal = 1 ' +
 220:                                 ') + ' +
 221:                             ''')'' ' +
 222:                         'ELSE '''' ' +
 223:                     'END ' +
 224:             'FROM sys.data_spaces AS d ' +
 225:             'WHERE ' +
 226:                 'd.data_space_id = i.data_space_id ' +
 227:         ') AS ds (data_space) ' +
 228:         'WHERE ' +
 229:             'i.object_id = @object_id ' +
 230:             --'AND i.is_unique = 1 ' +
 231:             'AND i.type >=1' +
 232:             --filtered and hypothetical indexes cannot be candidate keys
 233:             CASE @version
 234:                 WHEN '2008' THEN 'AND i.has_filter = 0 '
 235:                 ELSE ''
 236:             END +
 237:             'AND i.is_hypothetical = 0 ' +
 238:             'AND i.is_disabled = 0 ' +
 239:         'ORDER BY ' +
 240:             'i.index_id '
 241:  
 242:     --print @sql;
 243:     INSERT INTO  CreateTableKeys
 244:     EXEC sp_executesql @sql,  N'@object_id INT, @crlf CHAR(2)',
 245:         @object_id, @crlf
 246:  
 247:     INSERT INTO  CreateTableKeys
 248:     SELECT
 249:         'ALTER TABLE ' + 
 250:             QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id)) + '.' + 
 251:             QUOTENAME(OBJECT_NAME(fk.parent_object_id)) + @crlf +
 252:         CASE fk.is_not_trusted
 253:             WHEN 0 THEN 'WITH CHECK '
 254:             ELSE 'WITH NOCHECK '
 255:         END + 
 256:             'ADD ' +
 257:                 CASE fk.is_system_named
 258:                     WHEN 0 THEN 'CONSTRAINT ' + QUOTENAME(name) + @crlf
 259:                     ELSE ''
 260:                 END +
 261:         'FOREIGN KEY ' + @crlf + 
 262:         '( ' + @crlf + 
 263:             STUFF
 264: (
 265: (
 266:                     SELECT
 267: (
 268:                         SELECT 
 269:                             ',' + @crlf + ' ' + QUOTENAME(c.name) AS [text()]
 270:                         FROM sys.foreign_key_columns AS fc
 271:                         JOIN sys.columns AS c ON
 272:                             c.object_id = fc.parent_object_id
 273:                             AND c.column_id = fc.parent_column_id
 274:                         WHERE 
 275:                             fc.constraint_object_id = fk.object_id
 276:                         ORDER BY
 277:                             fc.constraint_column_id
 278:                         FOR XML PATH(''), TYPE
 279:                     ).value('.', 'VARCHAR(MAX)')
 280:                 ),
 281:                 1,
 282:                 3,
 283:                 ''
 284:             ) + @crlf + 
 285:         ') ' +
 286:         'REFERENCES ' + 
 287:             QUOTENAME(OBJECT_SCHEMA_NAME(fk.referenced_object_id)) + '.' + 
 288:             QUOTENAME(OBJECT_NAME(fk.referenced_object_id)) + @crlf +
 289:         '( ' + @crlf + 
 290:             STUFF
 291: (
 292: (
 293:                     SELECT
 294: (
 295:                         SELECT 
 296:                             ',' + @crlf + ' ' + QUOTENAME(c.name) AS [text()]
 297:                         FROM sys.foreign_key_columns AS fc
 298:                         JOIN sys.columns AS c ON
 299:                             c.object_id = fc.referenced_object_id
 300:                             AND c.column_id = fc.referenced_column_id
 301:                         WHERE 
 302:                             fc.constraint_object_id = fk.object_id
 303:                         ORDER BY
 304:                             fc.constraint_column_id
 305:                         FOR XML PATH(''), TYPE
 306:                     ).value('.', 'VARCHAR(MAX)')
 307:                 ),
 308:                 1,
 309:                 3,
 310:                 ''
 311:             ) + @crlf + 
 312:         ');
 313:         GO' + 
 314:             @crlf + @crlf COLLATE database_default AS [-- Create Referencing FKs]
 315:     FROM sys.foreign_keys AS fk
 316:     WHERE
 317:         referenced_object_id = @object_id
 318:         AND is_disabled = 0
 319:     ORDER BY
 320:         key_index_id
 321:  
 322: END
 323:  
 324: GO
 325:  
 326:  

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

   1: --USE [DatabaseName]
   2: --GO
   3:  
   4:  
   5: SET ANSI_NULLS ON
   6: GO
   7:  
   8: SET QUOTED_IDENTIFIER ON
   9: GO
  10:  
  11: IF EXISTS (SELECT 1 FROM sysobjects WHERE id=OBJECT_ID(N'ScriptDropTableKeys') AND OBJECTPROPERTY(id, 'IsProcedure') =1)
  12:     DROP PROCEDURE ScriptDropTableKeys;
  13: GO
  14:  
  15: --===============================================================================================================
  16: --        ProcedureName        :            ScriptDropTableKeys
  17: --        Author                :            Raymund Macaalay    
  18: --        CreateDate            :            2011-09-11
  19: --        Description            :            删除数据库里指定表的Constraints,Primary Key, Foreign Key, Index 
  20: /*****************************************************************************************************************
  21:         Parameters            :                                    参数说明
  22: ******************************************************************************************************************
  23:         @table_name            :                    数据库用户表的名字
  24: ******************************************************************************************************************
  25:    Modified Date    Modified User     Version                 Modified Reason
  26: ******************************************************************************************************************
  27:     2013-11-06             Kerry         V01.00.00         修改生成脚本的输出方式,将其写入表DropTableKeys
  28:     2013-12-08             Kerry         V01.00.00         Fix掉脚本中一个小bug: 不生成删除非唯一索引的SQL Script
  29: *****************************************************************************************************************/
  30:  
  31: --==============================================================================================================
  32:  
  33: CREATE PROC [dbo].[ScriptDropTableKeys]
  34:     @table_name SYSNAME
  35: AS
  36: BEGIN
  37:     SET NOCOUNT ON
  38:  
  39:     --Note: Disabled keys and constraints are ignored
  40:     --TODO: Drop and re-create referencing XML indexes, FTS catalogs
  41:  
  42:     DECLARE @crlf CHAR(2)
  43:     SET @crlf = CHAR(13) + CHAR(10)
  44:     DECLARE @version CHAR(4)
  45:     SET @version = SUBSTRING(@@VERSION, LEN('Microsoft SQL Server') + 2, 4)
  46:     DECLARE @object_id INT
  47:     SET @object_id = OBJECT_ID(@table_name)
  48:     DECLARE @sql NVARCHAR(MAX)
  49:  
  50:     IF @version NOT IN ('2005', '2008')
  51:     BEGIN
  52:         RAISERROR('This script only supports SQL Server 2005 and 2008', 16, 1)
  53:         RETURN
  54:     END
  55:  
  56:     INSERT INTO dbo.DropTableKeys
  57:     SELECT
  58:         'ALTER TABLE ' + 
  59:             QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' + 
  60:             QUOTENAME(OBJECT_NAME(parent_object_id)) + @crlf +
  61:         'DROP CONSTRAINT ' + QUOTENAME(name) + ';' + 
  62:             @crlf + @crlf COLLATE database_default AS [-- Drop Referencing FKs]
  63:     FROM sys.foreign_keys
  64:     WHERE
  65:         referenced_object_id = @object_id
  66:         AND is_disabled = 0
  67:     ORDER BY
  68:         key_index_id DESC
  69:  
  70:     
  71:     SET @sql = '' +
  72:         'SELECT ' +
  73:             'statement AS [-- Drop Candidate Keys] ' +
  74:         'FROM ' +
  75:         '( ' +
  76:             'SELECT ' +
  77:                 'CASE ' +
  78:                     'WHEN 1 IN (i.is_unique_constraint, i.is_primary_key) THEN ' +
  79:                         '''ALTER TABLE '' + ' +
  80:                             'QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) + ''.'' + ' +
  81:                             'QUOTENAME(OBJECT_NAME(i.object_id)) + @crlf + ' +
  82:                         '''DROP CONSTRAINT '' + QUOTENAME(i.name) + '';'' + ' +
  83:                             '@crlf + @crlf COLLATE database_default ' +
  84:                     'ELSE ' +
  85:                         '''DROP INDEX '' + QUOTENAME(i.name) + @crlf + ' +
  86:                         '''ON '' + ' +
  87:                             'QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + ''.'' + ' +
  88:                             'QUOTENAME(OBJECT_NAME(object_id)) + '';'' + ' +
  89:                                 '@crlf + @crlf COLLATE database_default ' +
  90:                 'END AS statement, ' +
  91:                 'i.index_id ' +
  92:             'FROM sys.indexes AS i ' +
  93:             'WHERE ' +
  94:                 'i.object_id = @object_id ' +
  95:                 --'AND i.is_unique = 1 ' +
  96:                 ' AND i.type >=1' +
  97:                 --filtered and hypothetical indexes cannot be candidate keys
  98:                 CASE @version
  99:                     WHEN '2008' THEN 'AND i.has_filter = 0 '
 100:                     ELSE ''
 101:                 END +
 102:                 'AND i.is_hypothetical = 0 ' +
 103:                 'AND i.is_disabled = 0 ' +
 104:         ') AS x ' +
 105:         'ORDER BY ' +
 106:             'index_id DESC;'
 107:     
 108:     --PRINT @sql;        
 109:     INSERT INTO  dbo.DropTableKeys
 110:     EXEC sp_executesql @sql,
 111:         N'@object_id INT, @crlf CHAR(2)',
 112:         @object_id, @crlf
 113:  
 114: END
 115: GO
 116:  
 117:  

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

   1:  
   2: --USE [DW_ESQUEL]
   3: --GO
   4:  
   5:  
   6: SET ANSI_NULLS ON
   7: GO
   8:  
   9: SET QUOTED_IDENTIFIER ON
  10: GO
  11:  
  12: IF EXISTS (SELECT 1 FROM sysobjects WHERE id=OBJECT_ID(N'sp_change_collation_script') AND OBJECTPROPERTY(id, 'IsProcedure') =1)
  13:     DROP PROCEDURE sp_change_collation_script;
  14: GO
  15:  
  16: --===============================================================================================
  17: --        ProcedureName        :            sp_change_collation_script
  18: --        Author                :            Kerry    
  19: --        CreateDate            :            2013-11-6
  20: --        Description            :            组合、补全Raymund Macaalay的脚本,生成改变列排序规则的脚本 
  21: /*************************************************************************************************
  22:         Parameters            :                                    参数说明
  23: **************************************************************************************************
  24:         @table_name            :                    数据库用户表的名字
  25: **************************************************************************************************
  26:    Modified Date    Modified User     Version                 Modified Reason
  27: **************************************************************************************************
  28:     2013-11-6             Kerry         V01.00.00         
  29: *************************************************************************************************/
  30:  
  31: --===============================================================================================
  32: CREATE PROCEDURE [dbo].[sp_change_collation_script]
  33:         @CollationName SYSNAME
  34: AS
  35: BEGIN
  36:    
  37: SET NOCOUNT ON
  38: DECLARE @SQLText            VARCHAR(MAX) ;
  39: DECLARE @TableName            NVARCHAR(255);
  40: DECLARE @ColumnName            sysname         ;
  41: DECLARE @DataType            NVARCHAR(128);
  42: DECLARE @CharacterMaxLen    INT             ;
  43: DECLARE @IsNullable            VARCHAR(3)     ;
  44: DECLARE @CreateSqlRowNum    INT;
  45: DECLARE @DropSqlRowNum        INT;
  46:  
  47: DECLARE MyTableCursor        Cursor
  48: FOR 
  49: SELECT name FROM sys.tables WHERE [type] = 'U' and name <> 'sysdiagrams' ORDER BY name 
  50:  
  51:  
  52: IF NOT EXISTS ( SELECT  1
  53:                 FROM    dbo.sysobjects
  54:                 WHERE   id = OBJECT_ID(N'[dbo].[ChangeColCollation]')
  55:                         AND xtype = 'U' )
  56:     BEGIN 
  57:     
  58:         CREATE TABLE [dbo].[ChangeColCollation] ( SQL_TEXT VARCHAR(MAX) )
  59:     END
  60: ELSE
  61:     TRUNCATE TABLE [dbo].[ChangeColCollation];
  62:     
  63:     
  64: OPEN MyTableCursor;
  65: FETCH NEXT FROM MyTableCursor INTO @TableName
  66:  
  67:  
  68: WHILE @@FETCH_STATUS = 0
  69:     BEGIN
  70:         DECLARE MyColumnCursor Cursor
  71:         FOR 
  72:         SELECT COLUMN_NAME,DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
  73:             IS_NULLABLE from information_schema.columns
  74:             WHERE table_name = @TableName AND  (Data_Type LIKE '%char%' 
  75:             OR Data_Type LIKE '%text%') AND COLLATION_NAME <> @CollationName
  76:             ORDER BY ordinal_position 
  77:         Open MyColumnCursor
  78:  
  79:         FETCH NEXT FROM MyColumnCursor INTO @ColumnName, @DataType, 
  80:               @CharacterMaxLen, @IsNullable
  81:         WHILE @@FETCH_STATUS = 0
  82:             BEGIN
  83:             SET @SQLText = 'ALTER TABLE ' + @TableName + ' ALTER COLUMN [' + @ColumnName + '] ' + 
  84:               @DataType + '(' + CASE WHEN @CharacterMaxLen = -1 THEN 'MAX' ELSE CAST(@CharacterMaxLen AS VARCHAR(6)) END + 
  85:               ') COLLATE ' + @CollationName + ' ' + 
  86:               CASE WHEN @IsNullable = 'NO' THEN 'NOT NULL' ELSE 'NULL' END
  87:             --PRINT @SQLText 
  88:             
  89:             INSERT INTO ChangeColCollation
  90:             VALUES (@SQLText);
  91:  
  92:         FETCH NEXT FROM MyColumnCursor INTO @ColumnName, @DataType, 
  93:               @CharacterMaxLen, @IsNullable
  94:         END
  95:         CLOSE MyColumnCursor
  96:         DEALLOCATE MyColumnCursor
  97:  
  98: FETCH NEXT FROM MyTableCursor INTO @TableName
  99: END
 100: CLOSE MyTableCursor
 101: --DEALLOCATE MyTableCursor
 102:  
 103:  
 104: IF NOT EXISTS ( SELECT  1
 105:                 FROM    dbo.sysobjects
 106:                 WHERE   id = OBJECT_ID(N'[dbo].[CreateTableKeys]')
 107:                         AND xtype = 'U' )
 108:     BEGIN 
 109:     
 110:         CREATE TABLE [dbo].[CreateTableKeys] ( SQL_TEXT VARCHAR(MAX) )
 111:     END
 112: ELSE
 113:     TRUNCATE TABLE [dbo].[CreateTableKeys];
 114:  
 115:   
 116:     
 117: IF NOT EXISTS ( SELECT  1
 118:                 FROM    dbo.sysobjects
 119:                 WHERE   id = OBJECT_ID(N'[dbo].[DropTableKeys]')
 120:                         AND XTYPE = 'U' )
 121:     BEGIN
 122:         CREATE TABLE dbo.DropTableKeys ( SQL_TEXT VARCHAR(MAX) )
 123:     END
 124: ELSE
 125:     TRUNCATE TABLE dbo.DropTableKeys;
 126:     
 127:     
 128:  
 129: OPEN MyTableCursor
 130:  
 131: FETCH NEXT FROM MyTableCursor INTO @TableName
 132: PRINT @TableName
 133: WHILE @@FETCH_STATUS = 0
 134:     BEGIN
 135:   
 136:      EXEC ScriptCreateTableKeys @TableName  --生成创建约束、索引等的脚本
 137:      EXEC ScriptDropTableKeys @TableName     --生成删除约束、索引等的脚本
 138:     FETCH NEXT FROM MyTableCursor INTO @TableName
 139: END
 140: CLOSE MyTableCursor
 141: DEALLOCATE MyTableCursor
 142:  
 143:  
 144: SELECT @CreateSqlRowNum = COUNT(1) FROM dbo.CreateTableKeys;
 145: SELECT @DropSqlRowNum = COUNT(1) FROM dbo.DropTableKeys;
 146:  
 147: IF @CreateSqlRowNum != @DropSqlRowNum
 148:     PRINT 'The table CreateTableKeys rows is different from the row of DropTableKeys ,please check the reason'
 149:  
 150:  
 151: END
 152: GO

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

   1:  
   2: ALTER DATABASE DataBase COLLATE Chinese_PRC_CI_AS
   3:  
   4: EXEC  sp_change_collation_script 'Chinese_PRC_CI_AS';
   5:  
   6: --执行下表里面的SQL语句
   7: SELECT * FROM dbo.DropTableKeys
   8:  
   9: --执行下表里面的SQL语句
  10: SELECT * FROM ChangeColCollation
  11:  
  12: --执行下表里面的SQL语句
  13: SELECT * FROM dbo.CreateTableKeys

 

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