Display difference in data between two exact schema tables.

GenerateTSQLForDeltaTable.sql

ALTER PROCEDURE [dbo].[GenerateTSQLForDeltaTable] AS
BEGIN

DECLARE @tables TABLE (tableschema nvarchar(16), tablename nvarchar(36), columnname nvarchar(64));

declare @staticDB nvarchar(32) = 'DeltaA';
declare @LiveDb nvarchar(32) = 'DeltaB';

insert into @tables
select t.TABLE_SCHEMA, t.TABLE_NAME, c.COLUMN_NAME from INFORMATION_SCHEMA.TABLES t
join INFORMATION_SCHEMA.COLUMNS c
ON t.TABLE_NAME = c.TABLE_NAME AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
WHERE c.ORDINAL_POSITION = 1;  -- can be replaced by PK

declare @sname nvarchar(16);
declare @tname nvarchar(36);
declare @cname nvarchar(64);

while exists (select 1 from @tables)
begin
	select top 1 @sname = tableschema,  @tname = tablename, @cname = columnname from @tables;
	
	exec DeltaTable01 @tname, @cname, @staticDB, @LiveDb;

	delete from @tables where tableschema = @sname AND @tname = tablename AND @cname = columnname;
end

END

TableDataDeltas.sql

ALTER PROCEDURE [dbo].[DeltaTable01] (
	@table nvarchar(100)
	, @PK_Column nvarchar(32) = 'Id'
	, @db01 nvarchar(20) = 'DeltaA'
	, @db02 nvarchar(30) = 'DeltaB'
) AS
/*
DeltaTable01 'Table01'
*/

declare @mainQuery as nvarchar(max);
declare @columnAliases as nvarchar(max);

SELECT @columnAliases = string_agg ('LiveTable.' + COLUMN_NAME + ' AS [' + COLUMN_NAME + '_Live] ', ', ' )  
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = @table;

SET @mainQuery = '
;with StaticTable as(
select * from ' + @db01 + '.dbo.' + @table + 
' except 
select * from ' + @db02 + '.dbo.' + @table +
' ),  LiveTable as(
select * from ' + @db02 + '.dbo.' + @table + 
' except 
select * from ' + @db01 + '.dbo.' + @table + 
')
select StaticTable.*,' + @columnAliases + ' from StaticTable full outer join LiveTable on StaticTable.' + @PK_Column + ' = LiveTable.' + @PK_Column +
' ORDER BY ' + @PK_Column + ', ' + @PK_Column + '_Live;'


print @mainquery;

--SELECT @mainQuery;

-- exec (@mainQuery);