概要

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