SQL - Compare tables and rows

-- =============================================
-- Author:      Asamco BV, Abby Aalinia
-- Create date: 27-11-2019
-- Description: to compare all data in all columns in two Tables/Databases based on the PK + showing column discrepancy
-- =============================================
IF OBJECT_ID('dbo._as_CompareAllColumns', 'P') IS NOT NULL
DROP PROCEDURE dbo._as_CompareAllColumns;
GO
CREATE PROCEDURE dbo._as_CompareAllColumns 
@tableName NVARCHAR(MAX),
@Schema NVARCHAR(MAX),
@DBNameSource NVARCHAR(MAX),
@DBNameTarget NVARCHAR(MAX)
AS
    SET NOCOUNT ON;
	DECLARE @sql NVARCHAR(MAX), @cols NVARCHAR(MAX) = N'';
	DECLARE @SourceColumns table(ColumnName NVARCHAR(MAX));
	DECLARE @TargetColumns table(ColumnName NVARCHAR(MAX));
	DECLARE @PK NVARCHAR(MAX);

	EXEC (N'Use ' + @DBNameSource);

	SELECT DISTINCT @PK = column_name FROM INFORMATION_SCHEMA.key_column_usage
		WHERE TABLE_NAME = @tableName;
	--SELECT @PK 

	INSERT INTO @SourceColumns SELECT name FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM '+@DBNameSource+'.'+@Schema+'.'+@tableName, NULL, 1);
	INSERT INTO @TargetColumns SELECT name FROM sys.dm_exec_describe_first_result_set(N'SELECT * FRt OM '+@DBNameTarget+'.'+@Schema+'.'+@tableName, NULL, 1);

	-- calculating column discrepancy
	SELECT sc.ColumnName AS ColumnOnlyExistsInSource,tc.ColumnName ColumnOnlyExistsInTarget 
	FROM @SourceColumns sc
		FULL JOIN @TargetColumns tc ON (sc.ColumnName = tc.ColumnName)
	WHERE sc.ColumnName IS NULL OR tc.ColumnName IS NULL

	-- calculating data mismatch
	SELECT @cols = 't1.'+@PK;
	SELECT @cols += N',case when t1.' + sc.ColumnName +  ' = t2.' + sc.ColumnName + ' then ''Match'' else ''Mismatch'' end as ' + sc.ColumnName + '_cmpr'  + CHAR(13)
	  FROM @SourceColumns sc INNER JOIN @TargetColumns tc ON sc.ColumnName = tc.ColumnName;

	--select @cols
	--SET @sql = N'Select '+ @cols + ' from ' + @DBNameSource+'.'+@Schema+'.'+@tableName  +' t1 JOIN '+ @DBNameTarget+'.'+@Schema+'.'+@tableName + ' t2 ON t1.' + @PK +  ' = t2.' + @PK

	EXEC (N'Select '+ @cols + ' from ' + @DBNameSource+'.'+@Schema+'.'+@tableName  +' t1 JOIN '+ @DBNameTarget+'.'+@Schema+'.'+@tableName + ' t2 ON t1.' + @PK +  ' = t2.' + @PK);

GO


/*
Sample usage:
Exec dbo._as_CompareAllColumns @tableName = N'Client',@Schema = N'dbo',@DBNameSource = N'[_ATT_LIVE_Latest]',@DBNameTarget = N'[_ATT_LIVE]'
*/

-- =============================================
-- Author:      Asamco BV, Abby Aalinia
-- Create date: 27-11-2019
-- Description: to compare all records in two Tables/Databases based on the PK
-- =============================================
IF OBJECT_ID('dbo._as_CompareRows', 'P') IS NOT NULL
DROP PROCEDURE dbo._as_CompareRows;
GO
CREATE PROCEDURE dbo._as_CompareRows 
@tableName NVARCHAR(MAX),
@Schema NVARCHAR(MAX),
@DBNameSource NVARCHAR(MAX),
@DBNameTarget NVARCHAR(MAX)
AS
    SET NOCOUNT ON;
	DECLARE @sql NVARCHAR(MAX),@PK NVARCHAR(MAX);

	EXEC (N'Use ' + @DBNameSource);

	SELECT DISTINCT @PK = column_name FROM INFORMATION_SCHEMA.key_column_usage
		WHERE TABLE_NAME = @tableName;

	--SELECT @PK 
	--SET @sql = N'Select '+ @cols + ' from ' + @DBNameSource+'.'+@Schema+'.'+@tableName  +' t1 JOIN '+ @DBNameTarget+'.'+@Schema+'.'+@tableName + ' t2 ON t1.' + @PK +  ' = t2.' + @PK

	EXEC (N'Select '''+@PK+''' as PKColumnName, '+'t1.'+ @PK + ' as RowOnlyExistsInSource,t2.'+ @PK + ' as RowOnlyExistsInTarget from ' + @DBNameSource+'.'+@Schema+'.'+@tableName  
			+' t1 FULL JOIN '+ @DBNameTarget+'.'+@Schema+'.'+@tableName + ' t2 ON t1.' + @PK +  ' = t2.' + @PK
			+ ' WHERE t1.'+ @PK + ' IS NULL OR t2.'+ @PK + ' IS NULL');

GO


/*
Sample usage:
Exec dbo._as_CompareRows @tableName = N'Client',@Schema = N'dbo',@DBNameSource = N'[_ATT_LIVE_Latest]',@DBNameTarget = N'[_ATT_LIVE]'
*/