全库修改SQL Server现有排序规则

浙江雁荡山

　　近日，在项目Debug过程中发现了SQL Server排序规则冲突的问题。
　　由于原数据库是从英文环境的SQL中生成的，其排序规则为“SQL_Latin1_General_CP1_CI_AS”，备份到本地中文环境之后，默认的排序规则为“Chinese_PRC_CI_AS”。本来对应的查询语句一直处于稳定的状态。但由于新增了字段，本地环境新增字段排序规则为“Chinese_PRC_CI_AS”，这时与原有的字段进行联查时会出现错误：
　　

无法解决 equal to 运算中 "SQL_Latin1_General_CP1_CI_AS" 和 "Chinese_PRC_CI_AS" 之间的排序规则冲突。　　

　　为解决排序规则冲突，可直接修改对应字段的排序规则，使其一致则可避免查询出错。
　　

ALTER TABLE [表名]>　　

　　但是数据库中还有很多排序为“SQL_Latin1_General_CP1_CI_AS”的字段，如果逐个去改，几个十几个字段的话还可以考虑，要是几十上百个工作量可想而知。
　　我们可以先查询当前数据库的需要修改的字段，查询对应的表名、字段名、排序规则、字段类型、以及对应的长度等等。
　　

SELECT　　t.name
AS [Table],　　c.name
AS [Column],　　c.collation_name
AS [Collation],　　TYPE_NAME( c.system_type_id)
AS [TypeName],　　c.max_length
AS [TypeLength]　　
FROM sys.columns c
　　
RIGHT JOIN sys.tables t ON c.object_id = t.object_id
　　
WHERE c.collation_name IS NOT NULL  
　　

　　数据库查询的结果为437行...

　　所以，过多的修改量基本上是不可能手动去慢慢修改的，需要通过SQL查询结果统一修改。
　　我是将结果集插入到临时表中，在通过循环临时表，exec执行拼接SQL语句去修改每一个记录，具体代码如下：

　　

DECLARE @table NVARCHAR(128)　　
DECLARE @column NVARCHAR(128)
　　
DECLARE @type NVARCHAR(128)
　　
DECLARE @typeLenght NVARCHAR(128)
　　
DECLARE @sql NVARCHAR(MAX )
　　

　　
SET ROWCOUNT 0
　　

　　
SELECT NULL mykey,
　　c.name,
　　t.name AS [Table],
　　c.name AS [Column],
　　c.collation_name AS [Collation],
　　Type_name(c.system_type_id) AS [TypeName],
　　c.max_length AS [TypeLength]
　　
INTO #temp
　　
FROM sys.columns c
　　RIGHT JOIN sys.tables t
　　ON c.object_id = t.object_id
　　
WHERE c.collation_name IS NOT NULL
　　

　　

　　
SET ROWCOUNT 1
　　
UPDATE #temp SET mykey = 1
　　

　　
WHILE @@ROWCOUNT > 0
　　BEGIN
　　SET ROWCOUNT 0
　　

　　SELECT @table = [Table],
　　@column = [Column],
　　@type = TypeName,
　　@typeLenght = TypeLength
　　FROM #temp
　　WHERE mykey = 1
　　

　　IF CONVERT(INT, @typeLenght) > 0 AND ( @type = 'nvarchar' OR @type = 'nchar' )
　　BEGIN
　　SET @typeLenght=CONVERT(NVARCHAR(128), CONVERT(INT, @typeLenght) / 2)
　　END
　　IF @typeLenght = '-1'
　　BEGIN
　　SET @typeLenght='max'
　　END
　　


　　SET @sql='>　　+ @column + '] ' + @type + '(' + @typeLenght
　　+ ') COLLATE Chinese_PRC_CI_AS'
　　

　　

　　BEGIN TRY
　　EXEC(@sql)
　　END TRY
　　BEGIN CATCH
　　SELECT @sql AS [ASL],
　　Error_message() AS msg
　　END CATCH
　　

　　DELETE #temp
　　WHERE mykey = 1
　　

　　SET ROWCOUNT 1
　　

　　UPDATE #temp
　　SET mykey = 1
　　END
　　

　　
SET ROWCOUNT 0
　　

　　
DROP TABLE #temp
　　


View Code　　执行SQL，更新出错的try catch查询结果如下：

　　我们可看到只有寥寥的几个字段需要通过手动去修改，这些修改不成功的大部分是由于外键关联等原因，逐个排查即可。
　　至此，SQL已自动修改了大部分字段，大大的减少了工作量。