블로그 이미지
불쥐의 눈으로 본세상 ㏈ª ☞ β┖υΕJini.κR

카테고리

분류 전체보기 (234)
By β┖υΕJini (131)
㏈ª By β┖υΕJini (103)
Total279,526
Today106
Yesterday45

 

 



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

 

 

Posted by ㏈ª ☞ β┖υΕJini.κR

 



갑작스런  TEMPDB 의 데이터 파일 증가로 인해 용량이 부족시...

 


-- DATABASE MDF 파일 축소 시도 (DB 정보로는 99% 축소 가능으로 보임)

 

USE [tempdb]

GO

dbcc shrinkfile (tempdev,10240)

-- 축소 되지 않음


--프로시저 캐시
DBCC FREEPROCCACHE
GO


-- Buffer Pool에 존재하는 데이터 페이지를 제거
DBCC DROPCLEANBUFFERS
Go


--사용 하지 않는 모든 캐시 삭제
DBCC FREESYSTEMCACHE ('ALL')
GO

--분산 쿼리에서 Microsoft SQL Server 인스턴스에 대해 사용한 분산 쿼리 연결 캐시를 플러시 DBCC FREESESSIONCACHE
GO

dbcc shrinkfile (tempdev,10240)
GO

Posted by ㏈ª ☞ β┖υΕJini.κR

 

 

 

 

여러 서버의 고유 값을 만들때 유용 할듯 합니다.

 

Windows가 시작된 이후에 지정한 컴퓨터에서 이 함수가 이전에 생성한 모든 GUID보다 큰 GUID를 만듭니다. Windows를 다시 시작한 후 GUID가 더 낮은 범위에서 다시 시작될 수 있지만 여전히 전역적으로 고유합니다. GUID 열이 행 식별자로 사용되는 경우 NEWSEQUENTIALID를 사용하면 NEWID 함수를 사용할 때보다 더 빠를 수 있습니다. 그 이유는 NEWID 함수의 경우 임의 작업이 발생하고 캐시된 데이터 페이지를 거의 사용하지 않기 때문입니다. 또한 NEWSEQUENTIALID를 사용하면 데이터 및 인덱스 페이지를 완전히 채울 수 있습니다.

 

CREATE TABLE myTable (ColumnA uniqueidentifier DEFAULT NEWSEQUENTIALID())

NEWSEQUENTIALID()를 사용하여 생성된 각 GUID는 해당 컴퓨터에서 고유합니다. NEWSEQUENTIALID()를 사용하여 생성된 GUID는 원본 컴퓨터에 네트워크 카드가 있는 경우에만 여러 컴퓨터에서 고유합니다. GUID에 대한 자세한 내용은 uniqueidentifier 데이터 사용을 참조하십시오.

 

Posted by ㏈ª ☞ β┖υΕJini.κR

2004년쯤 업어온 1995년산 IBM 키보드 한 3년 쓰다가 다시 쓰기로 결정^^

예전에 같이 일하던 형이 시끄러워 해서 체리로 갈아 탔다가 다시 쓸려고 오늘 회사에 가져 왔습니다.

한번 다 뜯어서 세척해서 그런지 18년된 키보드지만 아직 깨끗 하네요.

 

그런데 이런 이런... 윈도우  7 을 사용 하면서 키보드의   Windows Log Key  를 잘 사용 했는데 구형 키보드에 없어서 난감한 상황이 발생 하였네요...

 

이렇게 많은 기능을 가진 윈도우키를 포기할수가없어^^

하지만 구글 검색 신공으로 해결.~~~~

KeyTWeek 를 이용하여 오른쪽  Ctrl 를 키를 윈도우 키로 대체 성공~~~~ 우왕 좋다.. 

KeyTweak.zip

Posted by ㏈ª ☞ β┖υΕJini.κR