原文: SQL SERVER修改排序規則——腳本篇 在上篇 MS SQL 排序規則總結
中，大緻就資料庫伺服器排序規則（或者叫資料庫執行個體排序規則）、資料庫排序規則、列的排序規則粗淺的叙說了一遍，重點講述了修改資料庫伺服器排序規則（資料庫執行個體排序規則），其中對于資料庫排序規則的修改隻是粗略帶過。其實相對而言，修改伺服器排序規則（資料庫執行個體排序規則）相對簡單一些，修改資料庫的排序規則就複雜多了，因為涉及到資料、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')
如上所示，修改列的排序規則當中，如果在這個字段上建有索引，那麼修改列的排序規則時，就會報上面錯誤資訊。這時需要先删除索引，修改列的排序規則後，然後重建索引。
是以要徹底修改這些列的排序規則，這項工作相當的繁瑣和郁悶，還是推薦大家看看這位兄台的
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等表。修改資料庫的排序規則完成。
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)