概要
SQLのみで2つのデータベースを比較し、差異があるものを出力します。
結果サンプル
比較できるもの
- テーブル名
- 列名
- データ型
- NULL許容
- 主キー
実行環境
SQL Server 2014
結果サンプルで使用したデータベースの内容
データベース
- main_db
- sub_db
テーブル
テーブル名 | main_db | sub_db | 備考 |
---|---|---|---|
table(全て同じ) | ○ | ○ | 内容が同じ |
table(比較用) | ○ | ○ | 内容が違う |
table(main_dbのみ) | ○ | × | |
table(sub_dbのみ) | × | ○ |
table(比較用)の内容
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」がテーブル名に含まれていたら出力されません。)