본문 바로가기
㏈ª By β┖υΕJini/MS-SQL

데이터베이스의 모든 테이블의 콜레이션 변경 하기

by ㏈ª ☞ β┖υΕJini.κR 2013. 4. 18.

 

 



글로벌 관련 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 생성