Compare Table Contents Procedure

USE [YourDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[Compare_Table_Content](
@SourceDB varchar(100),
@TargetDB varchar(100)= '',
@SourceSchema varchar(100),
@TargetSchema varchar(100) = '',
@SourceTable varchar(100),
@TargetTable varchar(100) = '',
@SourceColumn varchar(1000) = '',
@TargetColumn varchar(1000) ='',
@ExcludeColumn varchar(1000) = '',
@SortOrder varchar(100) ='',
@debug bit = 0)
AS
/* 
Script Info:
Author: Damir Sprecic, Be Think, Solve, Execute AG
Version: v1.0 (Initial Release), 20-08-2018
Description: Compares the Content of two Tables, only works if both tables have the same Datatypes
*/DECLARE @SQL varchar(max)
DECLARE @out varchar(MAX)
DECLARE @in varchar(MAX)
--If @TargetDB is empty get @SourceDB
If @TargetDB = '' SET @TargetDB = @SourceDB

--If @TargetSchema is empty get @SourceSchema
If @TargetSchema = '' SET @TargetSchema = @SourceSchema

--If @TargetTable is empty get @SourceTable
If @TargetTable = '' SET @TargetTable = @SourceTable

--If @SourceColumn is empty set '*'
If @SourceColumn = '' SET @SourceColumn = '*'

--If @TargetColumn is empty get @SourceColumn
If @TargetColumn = '' SET @TargetColumn = @SourceColumn

--IF @ExcludeColumn is empty set '*'
IF @ExcludeColumn = '' SET @ExcludeColumn = '*'

--IF @SortOrder is empty set '1'
IF @SortOrder = '' SET @SortOrder = '1'

--Check if Temporary Tables exist and Drop
IF OBJECT_ID('tempdb.dbo.##Resultset', 'U') IS NOT NULL
  DROP TABLE ##Resultset;

If OBJECT_ID('tempdb.dbo.##Result2', 'U') IS NOT NULL
  DROP TABLE ##Result2;

--Create Temporary Table and insert Resultsets

SET @IN = '(SELECT COLUMN_NAME INTO ##Resultset
FROM ['+ @SourceDB + '].[INFORMATION_SCHEMA].[COLUMNS]
WHERE TABLE_NAME = ('''+ @SourceTable + ''')
AND COLUMN_NAME != ('''+ @ExcludeColumn + '''))'

IF @debug = 1 Print @IN
EXEC ( @IN);
SELECT STUFF((SELECT ', ' + COLUMN_NAME FROM ##Resultset
FOR XML PATH('')),1,1,'')AS COLUMN_NAME INTO ##Result2
;
SET @out = (SELECT * FROM ##Result2)
SET @SourceColumn = @out;
SET @TargetColumn = @out;
if @debug = 1 PRINT @out

SET @SQL = '(SELECT CAST(''source'' as varchar(100)) as origin, ' + @SourceColumn + ' FROM [' + @SourceDB + '].[' + @SourceSchema + '].[' +@SourceTable + '] 
EXCEPT 
SELECT  CAST(''target'' as varchar(100)) as origin, ' + @TargetColumn + ' FROM [' + @TargetDB + '].[' + @TargetSchema + '].[' + @TargetTable + '])
union all
(SELECT CAST(''target'' as varchar(100)) as origin, ' + @TargetColumn + ' FROM [' + @TargetDB + '].[' + @TargetSchema + '].[' + @TargetTable + ']
EXCEPT
SELECT  CAST(''source'' as varchar(100)) as origin, ' + @SourceColumn + ' FROM [' + @SourceDB + '].[' + @SourceSchema + '].[' + @SourceTable + '])
ORDER BY '+ @SortOrder +''
If @debug = 1 PRINT @SQL
exec ( @SQL)

IF OBJECT_ID('tempdb.dbo.##Resultset', 'U') IS NOT NULL
  DROP TABLE ##Resultset;
  If OBJECT_ID('tempdb.dbo.##Result2', 'U') IS NOT NULL
  DROP TABLE ##Result2;


GO