原文:
SQL SERVER修改排序规则——脚本篇
在上篇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')
如上所示，修改列的排序规则当中，如果在这个字段上建有索引，那么修改列的排序规则时，就会报上面错误信息。这时需要先删除索引，修改列的排序规则后，然后重建索引。
所以要彻底修改这些列的排序规则，这项工作相当的繁琐和郁闷，还是推荐大家看看这位兄台的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等表。修改数据库的排序规则完成。
