２つのDBのデータの差分を動的にとるクエリ・２つのDBで追加・削除されてる列を抽出するクエリのメモ

SQLのカーソルを使って、動的にクエリを作って色々チェックするクエリをメモとして記載。

# ２つのDBのデータの差分を取得するクエリ
２つのDBの差分を取ります。
２つのDBで共通するテーブル名・列名を抽出して
SELECT分を動的に作成します。

「先月取ったDBと今日とったDBでどこに差異が起きてるの？」とか
「システムのバージョンアップに伴って
データ移行した時、差分を取ってデータ移行正しいか確認したい」
等の場面で使う。

動的なのでどのDBでも使える。


```qu.sql
set nocount on

select NULL
UNION ALL
select NULL
UNION ALL
select '２つのDBのデータの差分を取ります。'
UNION ALL
select '差分が発生しているテーブルを抽出します。DBの変数名に対象のDBを２つ指定してください。'
UNION ALL
select '差異が発生しているテーブルは[メッセージ]欄を参照してください。'
UNION ALL
select 'なお、oldDBとnewDBで共通している列の差分のみを抽出します。'
UNION ALL
select NULL
UNION ALL
select NULL

DECLARE @MainDB   varchar(50) = 'oldDB'
DECLARE @TargetDB   varchar(50) = 'newDB'

--カーソルの値を取得する変数宣言
DECLARE @Tbl_Name varchar(50)

--カーソル定義
DECLARE CUR_DIFF CURSOR FOR
	select name from sys.objects where type = 'U' order by name 

--カーソルオープン
OPEN CUR_DIFF;

--最初の1行目を取得して変数へ値をセット
FETCH NEXT FROM CUR_DIFF
INTO @Tbl_Name;

--データの行数分ループ処理を実行する

PRINT '差分が発生しているテーブルを抽出します。'
WHILE @@FETCH_STATUS = 0
BEGIN
	-- ========= ループ内の実際の処理 ここから===
		select'dummy'
		select'dummy'
		select @Tbl_Name

		--変数定義
		DECLARE @sql       varchar(max);
		DECLARE @TblNm      varchar(100)
		DECLARE @ColNm      varchar(100)
		DECLARE @DBNm      varchar(100)
		 
		--SQL文作成
		SET @sql = 
					' SELECT MainTBL.* FROM ( ' +
					'	SELECT ''' + @Tbl_Name + ''' as テーブル名,name as 列名,''' + @MainDB + ''' as データベース名' + 
					'	FROM   ' + @MainDB + '.sys.columns' + 
					'	WHERE  object_id = (SELECT object_id' + 
					'	                    FROM   ' + @MainDB + '.sys.tables' + 
					'	                    WHERE  name = ''' + @Tbl_Name + '''' + 
					'	                    )' +
					' ) MainTBL' +
					' LEFT JOIN (' +
					'	SELECT * FROM ( ' +
					'		SELECT ''' + @Tbl_Name + ''' as テーブル名,name as 列名,''' + @TargetDB + ''' as データベース名' + 
					'		FROM   ' + @TargetDB + '.sys.columns' + 
					'		WHERE  object_id = (SELECT object_id' + 
					'		                    FROM   ' + @TargetDB + '.sys.tables' + 
					'		                    WHERE  name = ''' + @Tbl_Name + '''' + 
					'		                    )' +
					'	) TarTBL' +
					' ) AS TarTBL ON MainTBL.列名 = TarTBL.列名 ' +
					' where TarTBL.列名 IS NOT NULL ' 
		 
		--カーソルを定義する
		EXEC('DECLARE cur CURSOR FOR ' + @sql)
		 
		--カーソルを開く
		OPEN cur
		 
		--次のレコードを取得する
		FETCH NEXT FROM cur INTO @TblNm, @ColNm,@DBNm
		 
		--レコードが存在する間ループする
		DECLARE @sql_ORDER  varchar(max)  
		DECLARE @sql_Column_Main  varchar(max)  
		DECLARE @sql_Column_Target  varchar(max)  
		DECLARE @rowcnt int = 0
		WHILE @@FETCH_STATUS = 0
		BEGIN
			--取得した値を表示する
			SET @rowcnt = @rowcnt + 1
			If @rowcnt = 1
				SET @sql_ORDER = ' ' +  @ColNm
			ELSE
				SET @sql_ORDER = @sql_ORDER + ',' + @ColNm

			If @rowcnt = 1
				SET @sql_Column_Main = ' Mdf.' +  @ColNm
			ELSE
				SET @sql_Column_Main = @sql_Column_Main + ',Mdf.' + @ColNm

			If @rowcnt = 1
				SET @sql_Column_Target = ' Tdf.' +  @ColNm
			ELSE
				SET @sql_Column_Target = @sql_Column_Target + ',Tdf.' + @ColNm

		  --次のレコードを取得する
		  FETCH NEXT FROM cur INTO @TblNm, @ColNm , @DBNm
		 
		END
		 
		--カーソルを閉じる
		CLOSE cur
		DEALLOCATE cur
		
		DECLARE @sql_Main varchar(max);
		DECLARE @sql_Target varchar(max);
		DECLARE @sql_Final_Main varchar(max);
		DECLARE @sql_Final_Target varchar(max);
		
		SET @sql_Main = ' SELECT ' + @sql_Column_Main + 
						' from ' + @MainDB + '.dbo.'  + @TblNm + ' as Mdf '
						 
		SET @sql_Target = ' SELECT ' + @sql_Column_Target + 
						  ' from ' + @TargetDB + '.dbo.'  + @TblNm  + ' as Tdf '
		

		SET @sql_Final_Main  = @sql_Main +  
					      ' EXCEPT ' + 
						  @sql_Target  + ' order by ' + @sql_ORDER
		
		EXEC(@sql_Final_Main)
		
		SET @sql_Final_Target  = @sql_Target + 
					      ' EXCEPT ' + 
						  @sql_Main   + ' order by ' + @sql_ORDER
		
		EXEC(@sql_Final_Target)

		
		DECLARE @count int = @@ROWCOUNT;
		If @count > 0
			PRINT '------------------------------' 
		If @count > 0
			PRINT @Tbl_Name + ' : ' + CONVERT(varchar,@count)  + '件の差異'
		If @count > 0
			PRINT '		' + @MainDB + ' : ' + @sql_Final_Main
		If @count > 0
			PRINT '		' + @TargetDB + ' : ' + @sql_Final_Target
		If @count > 0
			PRINT '------------------------------' 
		If @count > 0
			PRINT '   ' 
		If @count > 0
			PRINT '   ' 
		
	-- ========= ループ内の実際の処理 ここまで===

	--次の行のデータを取得して変数へ値をセット
	FETCH NEXT FROM CUR_DIFF
	INTO @Tbl_Name;

END

--カーソルを閉じる
CLOSE CUR_DIFF;
DEALLOCATE CUR_DIFF;

```

# ２つのDBの構造を比較して、異なる列が追加されてる列を抽出
２つのDBで共通している名称のテーブルの列構造を取得して、
差異がある列を取得する。なお、名称だけの差分を取るだけなので、
列の型やサイズを知りたいなら改造がひつよう。

なお、まともな企業なら、DB設計書があるはずなので
本来こんなクエリは必要ないが、
世の中まともじゃない企業が山ほどある。
知らぬ間に列が追加されていて
誰も把握してない場合などにつかう。


```qu.sql
set nocount on

select '２つのDBを比較して他方にしか存在しない列を抽出します。'

DECLARE @MainDB   varchar(50) = 'oldDB'
DECLARE @TargetDB   varchar(50) = 'newDB'

--カーソルの値を取得する変数宣言
DECLARE @Tbl_Name varchar(50)

--カーソル定義
DECLARE CUR_DIFF CURSOR FOR
	select name from sys.objects where type = 'U'

--カーソルオープン
OPEN CUR_DIFF;

--最初の1行目を取得して変数へ値をセット
FETCH NEXT FROM CUR_DIFF
INTO @Tbl_Name;

--データの行数分ループ処理を実行する
WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT '------------------------'
	PRINT @Tbl_Name
	-- ========= ループ内の実際の処理 ここから===
		EXEC(
			' SELECT MainTBL.* FROM ( ' +
			'	SELECT ''' + @Tbl_Name + ''' as テーブル名,name as 列名,''' + @MainDB + ''' as データベース名' + 
			'	FROM   ' + @MainDB + '.sys.columns' + 
			'	WHERE  object_id = (SELECT object_id' + 
			'	                    FROM   ' + @MainDB + '.sys.tables' + 
			'	                    WHERE  name = ''' + @Tbl_Name + '''' + 
			'	                    )' +
			' ) MainTBL' +
			' LEFT JOIN (' +
			'	SELECT * FROM ( ' +
			'		SELECT ''' + @Tbl_Name + ''' as テーブル名,name as 列名,''' + @TargetDB + ''' as データベース名' + 
			'		FROM   ' + @TargetDB + '.sys.columns' + 
			'		WHERE  object_id = (SELECT object_id' + 
			'		                    FROM   ' + @TargetDB + '.sys.tables' + 
			'		                    WHERE  name = ''' + @Tbl_Name + '''' + 
			'		                    )' +
			'	) TarTBL' +
			' ) AS TarTBL ON MainTBL.列名 = TarTBL.列名 ' +
			' where TarTBL.列名 IS NULL ' 
		)

	-- ========= ループ内の実際の処理 ここまで===

	--次の行のデータを取得して変数へ値をセット
	FETCH NEXT FROM CUR_DIFF
	INTO @Tbl_Name;

END

--カーソルを閉じる
CLOSE CUR_DIFF;
DEALLOCATE CUR_DIFF;
```