２つの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;