概要

SQLのみで2つのデータベースを比較し、差異があるものを出力します。
結果サンプル

データベースの差異が表形式で出力されます
比較できるもの

テーブル名
列名
データ型
NULL許容
主キー
実行環境

SQL Server 2014
結果サンプルで使用したデータベースの内容

データベース

main_db
sub_db
テーブル

テーブル名	main_db	sub_db	備考
table(全て同じ)	○	○	内容が同じ
table(比較用)	○	○	内容が違う
table(main_dbのみ)	○	×
table(sub_dbのみ)	×	○
table(比較用)の内容

main_db
sub_db
SQL文

WITH
-----------------------------------------------------
--テーブル名取得(main_db)
-----------------------------------------------------
main_table AS
(
    SELECT 
        tbl.TABLE_NAME
    FROM 
        main_db.INFORMATION_SCHEMA.TABLES tbl    
    WHERE 
        tbl.TABLE_TYPE = 'BASE TABLE'
)
-----------------------------------------------------
--テーブル名取得(sub_db)
-----------------------------------------------------
,sub_table AS
(
    SELECT 
        tbl.TABLE_NAME
    FROM 
        sub_db.INFORMATION_SCHEMA.TABLES tbl
    WHERE 
        tbl.TABLE_TYPE = 'BASE TABLE'
)
-----------------------------------------------------
--列名取得(main_db)
-----------------------------------------------------
,main_column AS
(
    SELECT 
        col.TABLE_NAME
        ,col.COLUMN_NAME 
        ,CASE   WHEN col.IS_NULLABLE  = 'NO' THEN 'NOT NULL' 
                ELSE 'NULL' 
         END AS IS_NULLABLE
        ,col.DATA_TYPE
        ,col.CHARACTER_MAXIMUM_LENGTH
        ,col.NUMERIC_PRECISION
        ,col.NUMERIC_SCALE
        ,col.DATETIME_PRECISION
    FROM 
        main_db.INFORMATION_SCHEMA.COLUMNS col
    LEFT JOIN 
        main_db.INFORMATION_SCHEMA.TABLES  tbl
    ON 
        tbl.TABLE_NAME = col.TABLE_NAME
    WHERE 
        tbl.TABLE_TYPE = 'BASE TABLE'
)
-----------------------------------------------------
--列名取得(sub_db)
-----------------------------------------------------
,sub_column AS
(
    SELECT 
        col.TABLE_NAME
        ,col.COLUMN_NAME 
        ,CASE   WHEN col.IS_NULLABLE  = 'NO' THEN 'NOT NULL' 
                ELSE 'NULL' 
         END AS IS_NULLABLE
        ,col.DATA_TYPE
        ,col.CHARACTER_MAXIMUM_LENGTH
        ,col.NUMERIC_PRECISION
        ,col.NUMERIC_SCALE
        ,col.DATETIME_PRECISION
    FROM 
        sub_db.INFORMATION_SCHEMA.COLUMNS col
    LEFT JOIN 
        sub_db.INFORMATION_SCHEMA.TABLES  tbl
    ON 
        tbl.TABLE_NAME = col.TABLE_NAME
    WHERE 
        tbl.TABLE_TYPE = 'BASE TABLE'
)
-----------------------------------------------------
--主キー取得(main_db)
-----------------------------------------------------
,main_pkey AS
(
    SELECT 
        ccu.TABLE_NAME
        ,ccu.COLUMN_NAME
    FROM 
        main_db.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu   
)
-----------------------------------------------------
--主キー取得(sub_db)
-----------------------------------------------------
,sub_pkey AS
(
    SELECT 
        ccu.TABLE_NAME
        ,ccu.COLUMN_NAME
    FROM 
        sub_db.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu   
)
-----------------------------------------------------
--テーブル差異(main_db ONLY)
-----------------------------------------------------
,main_table_diff AS 
(
    SELECT 
        main.TABLE_NAME AS TABLE_NAME
        ,'' AS MAIN_COLUMN_NAME
        ,'' AS MAIN_DATA_TYPE
        ,'' AS SUB_COLUMN_NAME
        ,'' AS SUB_DATA_TYPE
        ,'main_db ONLY' AS PROC_TYPE
        ,1 AS ORDER_NUM
    FROM
        main_table main
    LEFT JOIN           
        sub_table sub
    ON 
        sub.TABLE_NAME = main.TABLE_NAME
    WHERE 
        sub.TABLE_NAME IS NULL
)
-----------------------------------------------------
--テーブル差異(sub_db only)
-----------------------------------------------------
,sub_table_diff AS
(
    SELECT 
        main.TABLE_NAME AS TABLE_NAME
        ,'' AS MAIN_COLUMN_NAME
        ,'' AS MAIN_DATA_TYPE
        ,'' AS SUB_COLUMN_NAME
        ,'' AS SUB_DATA_TYPE
        ,'sub_db ONLY' AS PROC_TYPE
        ,2 AS ORDER_NUM
    FROM
        sub_table main
    LEFT JOIN           
        main_table sub
    ON 
        sub.TABLE_NAME = main.TABLE_NAME
    WHERE 
        sub.TABLE_NAME IS NULL
)
-----------------------------------------------------
--カラム差異(main_db ONLY)
-----------------------------------------------------
,main_column_diff AS
(
    SELECT 
        main.TABLE_NAME AS TABLE_NAME
        ,main.COLUMN_NAME AS MAIN_COLUMN_NAME
        ,CASE main.DATA_TYPE 
            WHEN 'decimal'  THEN CONVERT(varchar,main.DATA_TYPE)+'('+CONVERT(varchar,main.NUMERIC_PRECISION)+', '+CONVERT(varchar,main.NUMERIC_SCALE)+') '+CONVERT(varchar,main.IS_NULLABLE)
            WHEN 'numeric'  THEN CONVERT(varchar,main.DATA_TYPE)+'('+CONVERT(varchar,main.NUMERIC_PRECISION)+', '+CONVERT(varchar,main.NUMERIC_SCALE)+') '+CONVERT(varchar,main.IS_NULLABLE)
            WHEN 'varchar'  THEN CONVERT(varchar,main.DATA_TYPE)+'('+CONVERT(varchar,main.CHARACTER_MAXIMUM_LENGTH)+') '+CONVERT(varchar,main.IS_NULLABLE)
            WHEN 'nvarchar' THEN CONVERT(varchar,main.DATA_TYPE)+'('+CONVERT(varchar,main.CHARACTER_MAXIMUM_LENGTH)+') '+CONVERT(varchar,main.IS_NULLABLE)
            WHEN 'datetime' THEN CONVERT(varchar,main.DATA_TYPE)+' '+CONVERT(varchar,main.IS_NULLABLE)
            WHEN 'date'     THEN CONVERT(varchar,main.DATA_TYPE)+' '+CONVERT(varchar,main.IS_NULLABLE)
            WHEN 'int'      THEN CONVERT(varchar,main.DATA_TYPE)+' '+CONVERT(varchar,main.IS_NULLABLE)
            WHEN 'nchar'    THEN CONVERT(varchar,main.DATA_TYPE)+'('+CONVERT(varchar,main.CHARACTER_MAXIMUM_LENGTH)+') '+CONVERT(varchar,main.IS_NULLABLE)
            ELSE NULL
         END AS MAIN_DATA_TYPE
        ,'' AS SUB_COLUMN_NAME
        ,'' AS SUB_DATA_TYPE
        ,'main_db ONLY' AS PROC_TYPE
        ,3 AS ORDER_NUM
    FROM
        main_column main
    LEFT JOIN           
        sub_table subtbl
    ON 
        main.TABLE_NAME = subtbl.TABLE_NAME
    LEFT JOIN           
        sub_column sub
    ON 
        main.TABLE_NAME = sub.TABLE_NAME
    AND 
        main.COLUMN_NAME = sub.COLUMN_NAME
    WHERE 
        sub.COLUMN_NAME IS NULL
    AND 
        subtbl.TABLE_NAME IS NOT NULL
)
-----------------------------------------------------
--カラム差異(sub_db ONLY)
-----------------------------------------------------
,sub_column_diff AS
(
    SELECT 
        main.TABLE_NAME AS TABLE_NAME
        ,'' AS MAIN_COLUMN_NAME
        ,'' AS MAIN_DATA_TYPE
        ,main.COLUMN_NAME AS SUB_COLUMN_NAME
        ,CASE main.DATA_TYPE 
            WHEN 'decimal'  THEN CONVERT(varchar,main.DATA_TYPE)+'('+CONVERT(varchar,main.NUMERIC_PRECISION)+', '+CONVERT(varchar,main.NUMERIC_SCALE)+') '+CONVERT(varchar,main.IS_NULLABLE)
            WHEN 'numeric'  THEN CONVERT(varchar,main.DATA_TYPE)+'('+CONVERT(varchar,main.NUMERIC_PRECISION)+', '+CONVERT(varchar,main.NUMERIC_SCALE)+') '+CONVERT(varchar,main.IS_NULLABLE)
            WHEN 'varchar'  THEN CONVERT(varchar,main.DATA_TYPE)+'('+CONVERT(varchar,main.CHARACTER_MAXIMUM_LENGTH)+') '+CONVERT(varchar,main.IS_NULLABLE)
            WHEN 'nvarchar' THEN CONVERT(varchar,main.DATA_TYPE)+'('+CONVERT(varchar,main.CHARACTER_MAXIMUM_LENGTH)+') '+CONVERT(varchar,main.IS_NULLABLE)
            WHEN 'datetime' THEN CONVERT(varchar,main.DATA_TYPE)+' '+CONVERT(varchar,main.IS_NULLABLE)
            WHEN 'date'     THEN CONVERT(varchar,main.DATA_TYPE)+' '+CONVERT(varchar,main.IS_NULLABLE)
            WHEN 'int'      THEN CONVERT(varchar,main.DATA_TYPE)+' '+CONVERT(varchar,main.IS_NULLABLE)
            WHEN 'nchar'    THEN CONVERT(varchar,main.DATA_TYPE)+'('+CONVERT(varchar,main.CHARACTER_MAXIMUM_LENGTH)+') '+CONVERT(varchar,main.IS_NULLABLE)
            ELSE NULL
         END AS SUB_DATA_TYPE
        ,'sub_db ONLY' AS PROC_TYPE
        ,4 AS ORDER_NUM
    FROM
        sub_column main
    LEFT JOIN           
        main_table subtbl
    ON 
        main.TABLE_NAME = subtbl.TABLE_NAME

    LEFT JOIN           
        main_column sub
    ON 
        main.TABLE_NAME = sub.TABLE_NAME
    AND 
        main.COLUMN_NAME = sub.COLUMN_NAME
    WHERE 
        sub.COLUMN_NAME IS NULL
    AND 
        subtbl.TABLE_NAME IS NOT NULL
)
-----------------------------------------------------
--データ型差異(NOT EQUAL)
-----------------------------------------------------
,data_type_diff AS
(
    SELECT 
        main.TABLE_NAME AS TABLE_NAME
        ,main.COLUMN_NAME AS MAIN_COLUMN_NAME
        ,CASE main.DATA_TYPE 
            WHEN 'decimal'  THEN CONVERT(varchar,main.DATA_TYPE)+'('+CONVERT(varchar,main.NUMERIC_PRECISION)+', '+CONVERT(varchar,main.NUMERIC_SCALE)+') '+CONVERT(varchar,main.IS_NULLABLE)
            WHEN 'numeric'  THEN CONVERT(varchar,main.DATA_TYPE)+'('+CONVERT(varchar,main.NUMERIC_PRECISION)+', '+CONVERT(varchar,main.NUMERIC_SCALE)+') '+CONVERT(varchar,main.IS_NULLABLE)
            WHEN 'varchar'  THEN CONVERT(varchar,main.DATA_TYPE)+'('+CONVERT(varchar,main.CHARACTER_MAXIMUM_LENGTH)+') '+CONVERT(varchar,main.IS_NULLABLE)
            WHEN 'nvarchar' THEN CONVERT(varchar,main.DATA_TYPE)+'('+CONVERT(varchar,main.CHARACTER_MAXIMUM_LENGTH)+') '+CONVERT(varchar,main.IS_NULLABLE)
            WHEN 'datetime' THEN CONVERT(varchar,main.DATA_TYPE)+' '+CONVERT(varchar,main.IS_NULLABLE)
            WHEN 'date'     THEN CONVERT(varchar,main.DATA_TYPE)+' '+CONVERT(varchar,main.IS_NULLABLE)
            WHEN 'int'      THEN CONVERT(varchar,main.DATA_TYPE)+' '+CONVERT(varchar,main.IS_NULLABLE)
            WHEN 'nchar'    THEN CONVERT(varchar,main.DATA_TYPE)+'('+CONVERT(varchar,main.CHARACTER_MAXIMUM_LENGTH)+') '+CONVERT(varchar,main.IS_NULLABLE)
            ELSE NULL
         END AS MAIN_DATA_TYPE
        ,sub.COLUMN_NAME AS SUB_COLUMN_NAME
        ,CASE sub.DATA_TYPE 
            WHEN 'decimal'  THEN CONVERT(varchar,sub.DATA_TYPE)+'('+CONVERT(varchar,sub.NUMERIC_PRECISION)+', '+CONVERT(varchar,sub.NUMERIC_SCALE)+') '+CONVERT(varchar,sub.IS_NULLABLE)
            WHEN 'numeric'  THEN CONVERT(varchar,sub.DATA_TYPE)+'('+CONVERT(varchar,sub.NUMERIC_PRECISION)+', '+CONVERT(varchar,sub.NUMERIC_SCALE)+') '+CONVERT(varchar,sub.IS_NULLABLE)
            WHEN 'varchar'  THEN CONVERT(varchar,sub.DATA_TYPE)+'('+CONVERT(varchar,sub.CHARACTER_MAXIMUM_LENGTH)+') '+CONVERT(varchar,sub.IS_NULLABLE)
            WHEN 'nvarchar' THEN CONVERT(varchar,sub.DATA_TYPE)+'('+CONVERT(varchar,sub.CHARACTER_MAXIMUM_LENGTH)+') '+CONVERT(varchar,sub.IS_NULLABLE)
            WHEN 'datetime' THEN CONVERT(varchar,sub.DATA_TYPE)+' '+CONVERT(varchar,sub.IS_NULLABLE)
            WHEN 'date'     THEN CONVERT(varchar,sub.DATA_TYPE)+' '+CONVERT(varchar,sub.IS_NULLABLE)
            WHEN 'int'      THEN CONVERT(varchar,sub.DATA_TYPE)+' '+CONVERT(varchar,sub.IS_NULLABLE)
            WHEN 'nchar'    THEN CONVERT(varchar,sub.DATA_TYPE)+'('+CONVERT(varchar,sub.CHARACTER_MAXIMUM_LENGTH)+') '+CONVERT(varchar,main.IS_NULLABLE)
            ELSE NULL
         END AS SUB_DATA_TYPE
        ,'NOT EQUAL' AS PROC_TYPE
        ,5 AS ORDER_NUM
    FROM
        main_column main
    LEFT JOIN           
        sub_column sub
    ON 
        main.TABLE_NAME = sub.TABLE_NAME
    AND 
        main.COLUMN_NAME = sub.COLUMN_NAME
    WHERE 
    (
        main.IS_NULLABLE <> sub.IS_NULLABLE
        OR 
        main.DATA_TYPE <> sub.DATA_TYPE
        OR 
        main.CHARACTER_MAXIMUM_LENGTH <> sub.CHARACTER_MAXIMUM_LENGTH
        OR 
        main.NUMERIC_PRECISION <> sub.NUMERIC_PRECISION
        OR 
        main.NUMERIC_SCALE <> sub.NUMERIC_SCALE
        OR 
        main.DATETIME_PRECISION <> sub.DATETIME_PRECISION
    )
)
-----------------------------------------------------
--主キー差異（main_db ONLY）
-----------------------------------------------------
,main_pkey_diff AS
(
    SELECT 
        main.TABLE_NAME AS TABLE_NAME
        ,'(PKEY) ' + main.COLUMN_NAME AS MAIN_COLUMN_NAME
        ,'' AS MAIN_DATA_TYPE
        ,'' AS SUB_COLUMN_NAME
        ,'' AS SUB_DATA_TYPE
        ,'main_db ONLY' AS PROC_TYPE
        ,6 AS ORDER_NUM
    FROM
        main_pkey main
    LEFT JOIN           
        sub_pkey sub
    ON 
        sub.TABLE_NAME = main.TABLE_NAME
    AND 
        sub.COLUMN_NAME = main.COLUMN_NAME
    LEFT JOIN           
        sub_table subtbl
    ON 
        subtbl.TABLE_NAME = main.TABLE_NAME
    WHERE 
        sub.TABLE_NAME IS NULL
    AND 
        subtbl.TABLE_NAME IS NOT NULL
)
-----------------------------------------------------
--主キー差異（sub_db ONLY）
-----------------------------------------------------
,sub_pkey_diff AS
(
    SELECT 
        main.TABLE_NAME AS TABLE_NAME
        ,'' AS MAIN_COLUMN_NAME
        ,'' AS MAIN_DATA_TYPE
        ,'(PKEY) ' + main.COLUMN_NAME AS SUB_COLUMN_NAME
        ,'' AS SUB_DATA_TYPE
        ,'sub_db ONLY' AS PROC_TYPE
        ,7 AS ORDER_NUM
    FROM
        sub_pkey main
    LEFT JOIN           
        main_pkey sub
    ON 
        sub.TABLE_NAME = main.TABLE_NAME
    AND 
        sub.COLUMN_NAME = main.COLUMN_NAME
    LEFT JOIN           
        main_table subtbl
    ON 
        subtbl.TABLE_NAME = main.TABLE_NAME
    WHERE 
        sub.TABLE_NAME IS NULL
    AND 
        subtbl.TABLE_NAME IS NOT NULL
)
-----------------------------------------------------
--SELECT
-----------------------------------------------------
SELECT
    diff.TABLE_NAME AS 'テーブル名'
    ,diff.MAIN_COLUMN_NAME AS 'main_db（列名）' 
    ,diff.MAIN_DATA_TYPE AS 'main_db（データ型）' 
    ,diff.SUB_COLUMN_NAME AS 'sub_db（列名）' 
    ,diff.SUB_DATA_TYPE AS 'sub_db（データ型）' 
    ,diff.PROC_TYPE AS '比較結果'
FROM
    (
        SELECT * FROM main_table_diff
        UNION ALL
        SELECT * FROM sub_table_diff
        UNION ALL
        SELECT * FROM main_column_diff
        UNION ALL
        SELECT * FROM sub_column_diff
        UNION ALL
        SELECT * FROM data_type_diff
        UNION ALL
        SELECT * FROM main_pkey_diff
        UNION ALL
        SELECT * FROM sub_pkey_diff
    ) diff

--*********************************************************************
-- WHERE句
-- 結果の絞り込みを行いたい場合は、ここに記述してください。

WHERE 
    diff.TABLE_NAME NOT LIKE '%[_]old%'
AND
    diff.TABLE_NAME NOT LIKE '%[_]tmp%'
--*********************************************************************

ORDER BY 
    diff.TABLE_NAME
    ,diff.ORDER_NUM
    ,diff.MAIN_COLUMN_NAME
    ,diff.SUB_COLUMN_NAME
使用する場合

「main_db」、「sub_db」をそれぞれ比較したいデータベース名に置換してください。
結果の絞り込みを行いたい場合は、WHERE句に追記してください。（現在は、「_old」、「_tmp」がテーブル名に含まれていたら出力されません。）
【SQL Server】2つのデータベースを比較し、差異を表形式で出力する

概要

結果サンプル

比較できるもの

実行環境

結果サンプルで使用したデータベースの内容

データベース

テーブル

table(比較用)の内容

SQL文

使用する場合
