kikki's tech note

技術ブログです。UnityやSpine、MS製品など技術色々について解説しています。

【メモ書き】SQL Serverの全カラムに対して、照合順序を変更するクエリ

SQL Serverの照合順序を一括で変更する必要が発生し、クエリを作るのが面倒だったので、今後の備忘のためにメモ書きとして残しておきます。

目的

SQL Serverで既に運用が始まっていて、ただ後から照合順序を変更したいときに当てるクエリについて記載しています。

データベースを変更

詳細は、公式の以下記事を参照ください。
データベースの照合順序の設定または変更
クエリは以下の通りです。

ALTER DATABASE WNSF  
COLLATE Japanese_XJIS_100_CS_AS_KS_WS;  
GO

全テーブルの該当カラムを変更

詳細は、公式の以下記事を参照ください。
列の照合順序の設定または変更
クエリは以下の通りです。

declare @characterOrder nvarchar(128) = 'Japanese_XJIS_100_CS_AS_KS_WS'
declare @sqlString nvarchar(max);
declare @tableName nvarchar(128)
declare @columnName nvarchar(128)
declare @dataType nvarchar(128)
declare @prec nvarchar(128)
declare @nullable nvarchar(128)

DECLARE ColumnCursor CURSOR FOR
select so.name as tableName, sc.name as columnName, st.name as dataType, sc.prec as prec, sc.isnullable as nullable
from sysobjects so
join syscolumns sc on so.id=sc.id
join systypes st on sc.xtype=st.xtype
where so.type='U'
and
st.name in ('varchar', 'nvarchar', 'char', 'nchar', 'text', 'ntext')
order by so.name,sc.colorder
OPEN ColumnCursor;
FETCH NEXT FROM ColumnCursor INTO @tableName, @columnName, @dataType, @prec, @nullable;
WHILE @@FETCH_STATUS = 0
   BEGIN
      SET @sqlString = 'ALTER TABLE ' + @tableName + ' ALTER COLUMN ' + @columnName + ' ' + @dataType + (CASE WHEN @dataType = 'text' THEN '' WHEN @dataType = 'ntext' THEN '' ELSE '(' + @prec + ')' END) + ' COLLATE ' + @characterOrder + ' ' + (CASE WHEN @nullable = 0 THEN 'NOT NULL' WHEN @nullable = 1 THEN 'NULL' END) + ';';
	  PRINT @sqlString;
	  EXEC(@sqlString)
      FETCH NEXT FROM ColumnCursor INTO @tableName, @columnName, @dataType, @prec, @nullable;  
   END;
CLOSE ColumnCursor;
DEALLOCATE ColumnCursor;
GO

手順としては、全テーブルの全カラムを抽出し、対象のカラムに対して変更クエリを作成し実行しています。
なお注意点として、このクエリはカラムに制約(UNIQUE、FK、INDEX等)があると途中でエラーとなります。事前に制約を解除してから実行してください。

※無断転載禁止 Copyright (C) 2015 kikkisnrdec All Rights Reserved.