MSSQL : DB간에 변경사항 체크하기

 

By ChatGPT, Claude

 

DB 이전이나 DB서버 업그레이드하면서 자동으로 바뀌는 경우가 있습니다.

변경된 사항을 찾아서 알려줍니다.

 

 

-- 변수 선언
DECLARE @sourceDatabase NVARCHAR(50) = 'wowdb777'; -- 소스 데이터베이스 이름
DECLARE @targetDatabase NVARCHAR(50) = 'wowdb888';   -- 타겟 데이터베이스 이름
DECLARE @sourceLabel NVARCHAR(50) = '2008R2';        -- 소스 데이터베이스 멘트
DECLARE @targetLabel NVARCHAR(50) = '2022';          -- 타겟 데이터베이스 멘트

-- 데이터 타입 변경사항 확인
DECLARE @query NVARCHAR(MAX);
SET @query = '
SELECT 
    gg_old.TABLE_NAME AS ''Table Name'',
    gg_old.COLUMN_NAME AS ''Column Name'',
    gg_old.DATA_TYPE AS [' + @sourceLabel + '_Type],
    gg_new.DATA_TYPE AS [' + @targetLabel + '_Type],
    gg_old.CHARACTER_MAXIMUM_LENGTH AS [' + @sourceLabel + '_Length],
    gg_new.CHARACTER_MAXIMUM_LENGTH AS [' + @targetLabel + '_Length],
    gg_old.NUMERIC_PRECISION AS [' + @sourceLabel + '_Precision],
    gg_new.NUMERIC_PRECISION AS [' + @targetLabel + '_Precision]
FROM 
    [' + @sourceDatabase + '].INFORMATION_SCHEMA.COLUMNS gg_old
    LEFT JOIN [' + @targetDatabase + '].INFORMATION_SCHEMA.COLUMNS gg_new 
        ON gg_old.TABLE_NAME = gg_new.TABLE_NAME 
        AND gg_old.COLUMN_NAME = gg_new.COLUMN_NAME
WHERE 
    gg_old.DATA_TYPE != gg_new.DATA_TYPE 
    OR gg_old.CHARACTER_MAXIMUM_LENGTH != gg_new.CHARACTER_MAXIMUM_LENGTH
    OR gg_old.NUMERIC_PRECISION != gg_new.NUMERIC_PRECISION
ORDER BY 
    gg_old.TABLE_NAME, gg_old.COLUMN_NAME;

-- Stored Procedure changes
SELECT 
    gg_old.SPECIFIC_NAME AS ''Procedure Name'',
    gg_old.ROUTINE_DEFINITION AS [' + @sourceLabel + '_Definition],
    gg_new.ROUTINE_DEFINITION AS [' + @targetLabel + '_Definition]
FROM 
    [' + @sourceDatabase + '].INFORMATION_SCHEMA.ROUTINES gg_old
    LEFT JOIN [' + @targetDatabase + '].INFORMATION_SCHEMA.ROUTINES gg_new 
        ON gg_old.SPECIFIC_NAME = gg_new.SPECIFIC_NAME
WHERE 
    gg_old.ROUTINE_DEFINITION != gg_new.ROUTINE_DEFINITION
    AND gg_old.ROUTINE_TYPE = ''PROCEDURE''
ORDER BY 
    gg_old.SPECIFIC_NAME;

-- Function changes
SELECT 
    gg_old.SPECIFIC_NAME AS ''Function Name'',
    gg_old.ROUTINE_DEFINITION AS [' + @sourceLabel + '_Definition],
    gg_new.ROUTINE_DEFINITION AS [' + @targetLabel + '_Definition]
FROM 
    [' + @sourceDatabase + '].INFORMATION_SCHEMA.ROUTINES gg_old
    LEFT JOIN [' + @targetDatabase + '].INFORMATION_SCHEMA.ROUTINES gg_new 
        ON gg_old.SPECIFIC_NAME = gg_new.SPECIFIC_NAME
WHERE 
    gg_old.ROUTINE_DEFINITION != gg_new.ROUTINE_DEFINITION
    AND gg_old.ROUTINE_TYPE = ''FUNCTION''
ORDER BY 
    gg_old.SPECIFIC_NAME;

-- Missing indexes
SELECT 
    gg_old.TABLE_NAME AS ''Table Name'',
    gg_old.INDEX_NAME AS ''Index Name'',
    gg_old.COLUMN_NAME AS ''Column Name''
FROM 
    (SELECT 
        t.name AS TABLE_NAME,
        ind.name AS INDEX_NAME,
        col.name AS COLUMN_NAME
    FROM 
        [' + @sourceDatabase + '].sys.indexes ind 
        INNER JOIN [' + @sourceDatabase + '].sys.index_columns ic 
            ON ind.object_id = ic.object_id and ind.index_id = ic.index_id
        INNER JOIN [' + @sourceDatabase + '].sys.columns col 
            ON ic.object_id = col.object_id and ic.column_id = col.column_id
        INNER JOIN [' + @sourceDatabase + '].sys.tables t 
            ON ind.object_id = t.object_id) gg_old
    LEFT JOIN 
    (SELECT 
        t.name AS TABLE_NAME,
        ind.name AS INDEX_NAME,
        col.name AS COLUMN_NAME
    FROM 
        [' + @targetDatabase + '].sys.indexes ind 
        INNER JOIN [' + @targetDatabase + '].sys.index_columns ic 
            ON ind.object_id = ic.object_id and ind.index_id = ic.index_id
        INNER JOIN [' + @targetDatabase + '].sys.columns col 
            ON ic.object_id = col.object_id and ic.column_id = col.column_id
        INNER JOIN [' + @targetDatabase + '].sys.tables t 
            ON ind.object_id = t.object_id) gg_new
    ON gg_old.TABLE_NAME = gg_new.TABLE_NAME 
    AND gg_old.INDEX_NAME = gg_new.INDEX_NAME
WHERE 
    gg_new.INDEX_NAME IS NULL
ORDER BY 
    gg_old.TABLE_NAME, gg_old.INDEX_NAME;
';

-- Execute
EXEC sp_executesql @query;