글로벌 관련 DB를 관리 하다 보면 콜레이션 이슈가 가끔 발생 하게 된다. 테이블간의 조인을 해야 하는데 테이블의 컬럼 콜레이션이 잘못 되어 컬럼절에 기준이 되는 컬럼의 콜레이션 변경하여 조인 하는 경우 많은 있었다.
여러나라에서 협업으로 개발하다 보니 콜레이션이 불일치 가는 경우가 발생하여 이번에는 그런 콜레이션 정보를 일일이 찾아 가면서 바꾸는 방법 말고 한꺼번에 바꿀수 있는 방법에 대해서 알아 보고자 한다.
참고 사이트는
http://www.codeproject.com/Articles/302405/The-Easy-way-of-changing-Collation-of-all-Database#
1. 콜레이션을 바꿀려면 PK, FK 를 제거 해야 한다. 하지만 다시 만들어야 하기 때문에 notepad 나 에디터 툴로 파일로 저장 해두어야 한다. 저장을 하기 위한 CreateCreateTableKeys SP 를 통해 스크립트를 저장 하도록 한다.
* 콜레이션 변경 후 생성에 사용할 PK, FK 스크립트 생성
|
/* Script Table Keys (C) 2010 Adam Machanic - amachanic@gmail.com http://sqlblog.com/blogs/adam_machanic/archive/2010/04/04/ rejuvinated-script-creates-and-drops-for-candidate-keys -and-referencing-foreign-keys.aspx This script produces a script of all of the candidate keys (primary keys or unique constraints) as well as referencing foreign keys, for the target table. To use, put SSMS into "results in text" mode and run the script. The output will be a formatted script that you can cut and paste to use elsewhere.
Don't forget to configure the maximum text size before using. The default is 256 characters--not enough for many cases.
Tools->Options->Query Results->Results to Text->Maximum number of characters->8192 */ CREATE PROC [dbo].[ScriptCreateTableKeys] @table_name SYSNAME 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 UNIQUE '' + 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 ' + --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 '
EXEC sp_executesql @sql, N'@object_id INT, @crlf CHAR(2)', @object_id, @crlf
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 + ');' + @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 |
* 생성한 스크립트를 활용 하여 백업
혹시 Results To TEXT 시 한 ROW 의 행이 255 가 넘을수있기 때문에 옵션을 먼저 변경 하고 아래 쿼리를 실행 한다.
* SSMS 에서 Ctrl+T 실행 한 후 아래 커서를 실행 한다.
| DECLARE @TableName nvarchar(255) DECLARE MyTableCursor Cursor FOR SELECT name FROM sys.tables WHERE [type] = 'U' and name <> 'sysdiagrams' ORDER BY name OPEN MyTableCursor
FETCH NEXT FROM MyTableCursor INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN EXEC dbo.ScriptCreateTableKeys @TableName
FETCH NEXT FROM MyTableCursor INTO @TableName END CLOSE MyTableCursor DEALLOCATE MyTableCursor |
콜레이션 변경 후 다시 만들어야 하는 PK,FK 을 위해 아래 Results To Text 를 저장 한다.
2. PK , FK 제거 스크립트를 생성 하고 Results To Text 결과 값으로 PK, FK 를 제거 한다.
* 콜레이션 변경 전 PK, FK 제거에 사용할 스크립트 생성
|
CREATE PROC [dbo].[ScriptDropTableKeys] @table_name SYSNAME 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
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 ' + --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 '
EXEC sp_executesql @sql, N'@object_id INT, @crlf CHAR(2)', @object_id, @crlf
END |
* 커서를 사용 하여 삭제 스크립트 추출
|
DECLARE @TableName nvarchar(255) DECLARE MyTableCursor Cursor FOR SELECT name FROM sys.tables WHERE [type] = 'U' and name <> 'sysdiagrams' ORDER BY name OPEN MyTableCursor
FETCH NEXT FROM MyTableCursor INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN EXEC ScriptDropTableKeys @TableName
FETCH NEXT FROM MyTableCursor INTO @TableName END CLOSE MyTableCursor DEALLOCATE MyTableCursor |
* 복사한 스크립트를 활용 하여 PK , FK 제거
3. 변경 하고자 하는 콜레이션으로 변경
| declare @TableName Nvarchar(4000),
@ColumnName Nvarchar(4000), @CharacterMaxLen Nvarchar(4000), @CollationName Nvarchar(4000), @IsNullable Nvarchar(4000), @DataType Nvarchar(4000), @SQLText Nvarchar(4000)
SET @CollationName = 'Latin1_General_CI_AS'
declare MyTableCursor cursor for SELECT name FROM sys.Tables
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 @CharacterMaxLen END + ') COLLATE ' + @CollationName + ' ' + CASE WHEN @IsNullable = 'NO' THEN 'NOT NULL' ELSE 'NULL' END PRINT @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 |
* Results To Text 의 내용을 복사 하여 콜레이션 변경
4. 1번의 백업해 놓은 Create PK , FK 를 사용 하여 PK, FK 생성
