全庫修改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 [表名] ALTER COLUMN [欄位名] nvarchar(256) COLLATE Chinese_PRC_CI_AS'

但是資料庫中還有很多排序為“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)--迴圈Item表名
DECLARE @column NVARCHAR(128)--迴圈Item欄位名
DECLARE @type NVARCHAR(128)--對應欄位的類型，char、nchar、varchar、nvarchar等
DECLARE @typeLenght NVARCHAR(128)--對應類型的長度，nchar、nvarchar需要將數值除於2
DECLARE @sql NVARCHAR(MAX )--要拼接執行的sql語句

 
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
--先測試Product表
--AND t.name='Product'

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

 --nchar、nvarchar需要將數值除於2
 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 

 --拼接sql，註意表名、欄位名要帶[]，避免Group等關鍵字
 SET @sql=' ALTER TABLE [' + @table + '] ALTER COLUMN ['
 + @column + '] ' + @type + '(' + @typeLenght
 + ') COLLATE Chinese_PRC_CI_AS'


 --Try執行
 BEGIN TRY
 EXEC(@sql)
 END TRY
 --Catch查詢異常結果
 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

執行SQL，更新出錯的try catch查詢結果如下：

我們可看到只有寥寥的幾個欄位需要通過手動去修改，這些修改不成功的大部分是由於外鍵關聯等原因，逐個排查即可。

至此，SQL已自動修改了大部分欄位，大大的減少了工作量。