8
9

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

【SQL Server】2つのデータベースを比較し、差異を表形式で出力する

Posted at

概要

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

結果サンプル

データベースの差異が表形式で出力されます
image.png

比較できるもの

  • テーブル名
  • 列名
  • データ型
  • NULL許容
  • 主キー

実行環境

SQL Server 2014

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

データベース

  • main_db
  • sub_db

テーブル

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

table(比較用)の内容

  • main_db
    image.png

  • sub_db
    image.png

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」がテーブル名に含まれていたら出力されません。)
8
9
1

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
8
9

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?