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

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

### 結果サンプル
データベースの差異が表形式で出力されます
![image.png](https://qiita-image-store.s3.amazonaws.com/0/317569/7afa7aa6-c845-4e24-7da3-4d0203be908a.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](https://qiita-image-store.s3.amazonaws.com/0/317569/1131646e-3a8d-5981-fe20-eb1223937d9a.png)

- sub_db
![image.png](https://qiita-image-store.s3.amazonaws.com/0/317569/e8208e7a-85f0-f289-76fc-cc661170a10e.png)


# SQL文

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