编写人：CC阿爸
2014-3-1
l 今年的一项重要工作是对公司所用系统进行繁简的转换，程序转成简体基本很容易解决，但数据库转换成简体，就没那么容易了。经测试发现，简体的数据库，可以完美的支持到繁简体同时存储，并且不用更换任何数据类型，没想到SQL升级到2005后，微软如此的厚爱大陆市场，其它的话不多说了，罗列一下，本次转换数据库的相关步骤，
从网上看到有部分好人，有相关的转换代码，其根源是老外写的，但往往直接拿来用是不行的，还必须对其进行修正，该代码只考虑了，约速束主键和外键，未考虑到检查约束，统计信息，因此决定还是写在博客中，方便日后查找，需要的同学可供参考。
经过在网上搜索部分解决方案，在此基础上进行修正，解决方案才是正解。
USE ECM_CN 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 : KevinZhang -- CreateDate : 2014-02-18 -- Description : 生成数据库里指定表的Constraints,Primary Key, Foreign Key, Index的创建脚本. /***************************************************************************************************************** Parameters : 参数说明 ****************************************************************************************************************** @table_name : 数据库用户表的名字 ****************************************************************************************************************** Modified Date Modified User Version Modified Reason ****************************************************************************************************************** 2013-11-06 KevinZhang V01.00.00 修改生成脚本的输出方式，将其写入表CreateTableKeys 2013-11-08 KevinZhang V01.00.01 Fix生成索引的一些bugs： 1: 非唯一索引不生成索引 2：索引type_des为HEAP的索引也会生成。 ******************************************************************************************************************/ --================================================================================================================ CREATE PROC [dbo].[ScriptCreateTableKeys] @table_name varchar(50) 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 创建删除指定表的约束、索引的脚本 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 : -- Author : KevinZhang -- 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 varchar(50) 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 创建修改列排序规则的脚本，以及循环调用[ScriptCreateTableKeys] 、ScriptDropTableKeys 生成对应的 --USE --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 : KevinZhang -- CreateDate : 2013-02-18 -- Description : 组合、补全Raymund Macaalay的脚本，生成改变列排序规则的脚本 /************************************************************************************************* Parameters : 参数说明 ************************************************************************************************** @table_name : 数据库用户表的名字 ************************************************************************************************** Modified Date Modified User Version Modified Reason ************************************************************************************************** 2013-11-6 KevinZhang V01.00.00 *************************************************************************************************/ --=============================================================================================== CREATE PROCEDURE [dbo].[sp_change_collation_script] @CollationName varchar(50) 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 HZEW2_CN COLLATE Chinese_PRC_BIN
EXEC sp_change_collation_script ‘Chinese_PRC_BIN‘;
--1.产生生成检查约束脚本 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 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 DECLARE c1 cursor for select object_id from sys.check_constraints where type = ‘C‘ open c1 fetch next from c1 into @object_id while(@@fetch_status=0) begin select @sql= ‘alter table [‘+ object_name(parent_object_id) + ‘] WITH NOCHECK ADD CONSTRAINT [‘+name+‘] check ‘ +definition from sys.check_constraints where type = ‘C‘ and object_id=@object_id INSERT INTO dbo.CreateTableKeys values(@sql+@crlf) fetch next from c1 into @object_id end close c1 deallocate c1
--2.产生删除检查约束脚本 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 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 DECLARE c1 cursor for select object_id from sys.check_constraints where type = ‘C‘ open c1 fetch next from c1 into @object_id while(@@fetch_status=0) begin select @sql= ‘alter table [‘+ object_name(parent_object_id) + ‘] drop constraint [‘+name+‘]; ‘ from sys.check_constraints where type = ‘C‘ and object_id=@object_id INSERT INTO dbo.DropTableKeys values(@sql+@crlf) fetch next from c1 into @object_id end close c1 deallocate c1
--3执行下表里面的SQL语句 SELECT * FROM dbo.DropTableKeys declare @sqltemp nVARCHAR(255),@id varchar(50) SELECT ID=newid(),*, handle=0 into #t1 from DropTableKeys while ((select count(*) from #t1 where handle=0)>0) begin select top 1 @id=ID,@sqltemp=SQL_TEXT from #t1 where handle=0 select @sqltemp as aa EXEC sp_executesql @sqltemp update #t1 set handle=1 where ID=@id end drop table #t1
--4执行下表里面的SQL语句 SELECT * FROM ChangeColCollation declare @sqltemp nVARCHAR(255),@id varchar(50) SELECT ID=newid(),*, handle=0 into #t1 from ChangeColCollation while ((select count(*) from #t1 where handle=0)>0) begin select top 1 @id=ID,@sqltemp=SQL_TEXT from #t1 where handle=0 EXEC sp_executesql @sqltemp update #t1 set handle=1 where ID=@id end drop table #t1
--5执行下表里面的SQL语句 SELECT * FROM dbo.CreateTableKeys declare @sqltemp nVARCHAR(255),@id varchar(50) SELECT ID=newid(),*, handle=0 into #t1 from CreateTableKeys while ((select count(*) from #t1 where handle=0)>0) begin select top 1 @id=ID,@sqltemp=SQL_TEXT from #t1 where handle=0 EXEC sp_executesql @sqltemp update #t1 set handle=1 where ID=@id end drop table #t1
/**************************************************************************************************************************************************************/ --6 最后验证没有问题后，可以删除dbo.CreateTableKeys、dbo.DropTableKeys、dbo.ChangeColCollation等表。修改数据库的排序规则完成。 drop table CreateTableKeys drop table DropTableKeys drop table ChangeColCollation drop proc ScriptCreateTableKeys drop proc [sp_change_collation_script] drop proc ScriptDropTableKeys
--7部分统计信息要先删除 /****** Object: Statistic [hind_318_1] Script Date: 02/18/2014 10:21:39 ******/ if exists (select * from sys.stats where name = N‘hind_318_1‘ and object_id = object_id(N‘[dbo].[PINVDTL]‘)) DROP STATISTICS [dbo].[PINVDTL].[hind_318_1] GO USE [HZEW2_CN] GO /****** Object: Statistic [hind_318_1] Script Date: 02/18/2014 10:21:40 ******/ CREATE STATISTICS [hind_318_1] ON [dbo].[PINVDTL]([PINV]) GO