Use Tools;
GO

create table T1(C1 int, C2 int)
create table T2(C1 int primary key, C2 int)

insert T1 values(0,0)
insert T1 values(0,0)
insert T1 values(1,1)
insert T1 values(1,1)
insert T1 values(2,2)
insert T1 values(3,3)
insert T1 values(4,null)

insert T2 values(1,1)
insert T2 values(2,2)
insert T2 values(3,3)
insert T2 values(4,4)


exec [sp_Compare] 
'Tools.dbo.T1'
,
'Tools.dbo.T2'
,
'not equal'

CREATE PROCEDURE [dbo].[sp_Compare] 

	  @tab1 nvarchar(max)				 --Обязательный параметер. Таблица или SQL Выражение (1). !!! Таблицы указывать в нотации [База].[Схема].[Таблица] или [Сервер].[База].[Схема].[Таблица]
	, @tab2 nvarchar(max)				 --Обязательный параметер. Таблица или SQL Выражение (2). !!! Таблицы указывать в нотации [База].[Схема].[Таблица] или [Сервер].[База].[Схема].[Таблица]
	, @operation nvarchar(10)			 --Обязательный параметер. Операция сравнения: equal,e,   not equal,n,  left,l,  right,r 		
	, @exclude_cols nvarchar(max)	= '' --Опциональный параметер. Столбцы, которые должны быть исключены из спавнения, через запятую
	, @options varchar(max)			= '' --Опциональный параметер. Опции, см. ниже
	-- Опции:
	-- <table_name> out[put]; имя маблицы для вывода пезультатов ставнения
	-- noexec;  вывести  динамич. SQL но не выполнять
	-- use_tempdb; результаты SQL Выражение (1,2) сохтанять в вретенных таблицах
	-- order by <col1>,...<coln> ; сортировать результаты
	--
	-- Замечания:
	-- Дубликаты при сравнении игнорируются
	--
	-- Метод сравнения взят из: Работа с множествами в Transact-SQL, часть 3': http://www.realcoding.net/articles/rabota-s-mnozhestvami-v-transact-sql.html
	--
	-- Столбец MASK в выводе:
	--  1 - Строка находится тольно в Таблице/Выражении 1; 
	--  2 - Строка находится тольно в Таблице/Выражении 2
	--  3 - Строка находится в обоих Таблицах/Выражениях
	
AS
BEGIN
	SET NOCOUNT ON;
    	
------------------------------

declare @tabcols1 table(cid int, name sysname, tid int)
declare @tabcols2 table(cid int, name sysname, tid int)
declare @from1 nvarchar(max)
declare @from2 nvarchar(max)
declare @colsextern nvarchar(max)
declare @colsintern1 nvarchar(max)
declare @colsintern2 nvarchar(max)
declare @sql nvarchar(max)
declare @op_expr nvarchar(100)
declare @server1 sysname, @database1 sysname, @schema1 sysname, @table1 sysname
declare @server2 sysname, @database2 sysname, @schema2 sysname, @table2 sysname
declare @temptabname1 sysname = '#' + replace(NEWID() ,'-','') 
declare @temptabname2 sysname = '#' + replace(NEWID() ,'-','') 
declare @xml xml
declare @exclude_cols_tab table(name sysname)
declare @options_tab table(value nvarchar(max)) 
declare @mask_column sysname = ''


--caller database
declare @caller_db sysname
select  @caller_db=isnull(db_name(resource_database_id),'master') FROM sys.dm_tran_locks WHERE request_session_id = @@SPID and resource_type = 'DATABASE' and request_owner_type = 'SHARED_TRANSACTION_WORKSPACE' and resource_database_id<>db_id()
set @caller_db=isnull(@caller_db,'master')

set @xml = '<c>'+replace(@exclude_cols,',','</c><c>')+'</c>'
insert @exclude_cols_tab select distinct parsename(ltrim(rtrim(a.b.value('.','sysname'))),1) from @xml.nodes('c') a(b) where a.b.value('.','sysname')<>''


set @xml = N'<o>'+replace(@options,N';',N'</o><o>')+N'</o>'
insert @options_tab select distinct ltrim(rtrim(a.b.value('.','sysname'))) from @xml.nodes('o') a(b)

if charindex('select', @tab1 ,0)>0
begin
	set @from1 = '(' + @tab1 + ')T'			
end
else
begin
  set @tab1=rtrim(ltrim(replace(replace(@tab1,char(10),''),char(13),'')))  
  set @server1		= parsename(@tab1,4)
  set @database1	= parsename(@tab1,3)
  set @schema1		= parsename(@tab1,2)
  set @table1		= parsename(@tab1,1)

  if @schema1 is NULL
    set @schema1 = 'dbo'


  if @database1 is NULL and @caller_db<>db_name()
    set @database1 = @caller_db
  	
	set @tab1 = '[' + @table1 + ']'

	if @schema1 is not null and @schema1 <> 'dbo'
		set @tab1 = '[' + @schema1 + '].[' + @table1 + ']'
 
	if @database1 is not null 
		set @tab1 = '[' + @database1 + '].[' + @schema1 + '].[' + @table1 + ']'

	if @server1 is not null 
		set @tab1 = '[' + @server1 + '].[' + @database1 + '].[' + @schema1 + '].[' + @table1 + ']'

  set @from1 = @tab1 + ' with(nolock)'
end 
	

if charindex('select', @tab2 ,0)>0
begin
	set @from2 = '(' + @tab2 + ')T'	
end
else
begin
  set @tab2=rtrim(ltrim(replace(replace(@tab2,char(10),''),char(13),'')))
  set @server2		= parsename(@tab2,4)
  set @database2	= parsename(@tab2,3)
  set @schema2		= parsename(@tab2,2)
  set @table2		= parsename(@tab2,1)

  if @schema2 is NULL
    set @schema2 = 'dbo'

  if @database2 is NULL and @caller_db<>db_name()
    set @database2 = @caller_db
  	
	set @tab2 = '[' + @table2 + ']'

	if @schema2 is not null and @schema2 <> 'dbo'
		set @tab2 = '[' + @schema2 + '].[' + @table2 + ']'
 
	if @database2 is not null 
		set @tab2 = '[' + @database2 + '].[' + @schema2 + '].[' + @table2 + ']'

	if @server2 is not null 
		set @tab2 = '[' + @server2 + '].[' + @database2 + '].[' + @schema2 + '].[' + @table2 + ']'

  set @from2 = @tab2 + ' with(nolock)'
end 


set @sql = 'use ['+@caller_db+'];
'
set @sql += 'select top 0 * into '+@temptabname1+' from ' + @from1 + ';'
set @sql += 'select column_id, name, user_type_id from tempdb.sys.columns where object_id=object_id(''tempdb.dbo.'+@temptabname1+''') order by column_id'


insert @tabcols1
exec sp_executesql @sql


set @sql = 'use ['+@caller_db+'];
'
set @sql += 'select top 0 * into '+@temptabname1+' from ' + @from2 + ';'
set @sql += 'select column_id, name, user_type_id from tempdb.sys.columns where object_id=object_id(''tempdb.dbo.'+@temptabname1+''') order by column_id'


insert @tabcols2
exec sp_executesql @sql

	
set @mask_column='mask'
while exists(select * from @tabcols1 where name = @mask_column) or exists(select * from @tabcols2 where name = @mask_column)
  set @mask_column += '_'

if @operation not in ('equal', 'e', 'not equal', 'n', 'left','l', 'right','r' )
begin
  raiserror ('allowed operations only: equal, e, not equal, n, left, l, right, r',16,1);
end
else
begin
	if @operation in ('equal', 'e')
		set @op_expr = 'sum(distinct '+@mask_column+') = 3'
	if @operation in ('not equal', 'n')
		set @op_expr = 'sum(distinct '+@mask_column+') <> 3'
	if @operation in ('left', 'l')
		set @op_expr = 'sum(distinct '+@mask_column+') = 1'
	if @operation in ('right', 'r')
		set @op_expr = 'sum(distinct '+@mask_column+') = 2'
end
 
select 
@colsintern1=
substring(
(select ',' + case when tid=34 then 'cast([' + name + '] as varbinary(max)) ' when tid=35 then 'cast([' + name + '] as varchar(max)) ' when tid in(99,241) then 'cast([' + name + '] as nvarchar(max)) ' else '['+name+']' end + ' as ['+ name+']'
from @tabcols1
where name not in (select name from @exclude_cols_tab)
order by cid for xml path('')
),2,50000) 



select 
@colsintern2=
substring(
(select ',' + case when tid=34 then 'cast([' + name + '] as varbinary(max)) ' when tid=35 then 'cast([' + name + '] as varchar(max)) ' when tid in(99,241) then 'cast([' + name + '] as nvarchar(max)) ' else '['+name+']' end + ' as ['+ name+']'
from @tabcols2
where name not in (select name from @exclude_cols_tab)
order by cid for xml path('')
),2,50000) 


select 
@colsextern=
substring(
(select ',[' + name + ']' 
from @tabcols1
where name not in (select name from @exclude_cols_tab)
order by cid for xml path('')
),2,50000) 

--Output In Table?
declare @out_server sysname, @out_database sysname, @out_schema sysname, @out_tab sysname, @out_param sysname, @out_full sysname, @out_temp bit
declare @insert_clause      nvarchar(max)
declare @insert_clause_into nvarchar(max)
declare @out_exists bit=0		
declare @out_identity_col sysname	
declare @sql_reset_out_identity nvarchar(1000) = ''

select  @out_param = value from @options_tab where value like '% OUT' or value like '% OUTPUT'
select  @out_param = ltrim(reverse(substring(reverse(@out_param), charindex(reverse(' out'),reverse(@out_param),0)+4, 200)))
if @out_param is not null
begin
	set @out_server		= parsename(@out_param,4)
	set @out_database	= parsename(@out_param,3)
	set @out_schema		= parsename(@out_param,2)
	set @out_tab		= parsename(@out_param,1)
	
	if @out_tab like '#%'
	begin
		set @out_database='tempdb'
		set @out_full = @out_tab
		set @out_temp = 1
	end	 
	else
	begin
	   set @out_temp = 0
	end

	if @out_server = @@SERVERNAME 
	  set @out_server = NULL

    if @out_temp=1 and @out_server is not null
	  raiserror ('operations with temp tables on local server only',16,1)

	if @out_database is null
	  set @out_database = @caller_db 

	if isnull(@out_schema,'') = ''
	  set @out_schema = 'dbo'
       		   	
	if @out_temp=0 and @out_server is not null 
		set @out_full = '[' + @out_server + '].[' + @out_database + '].[' + @out_schema + '].[' + @out_tab + ']'
    if @out_temp=0 and @out_server is     null 
	    set @out_full =                       '[' + @out_database + '].[' + @out_schema + '].[' + @out_tab + ']'
	if @out_temp=1
        set @out_full =                                                                     '[' + @out_tab + ']'
				        
	if @out_temp=0 and @out_server is not null	  
	  set @sql = N'select @out_exists = count(*) where exists(select * from ['+@out_server+'].['+@out_database+'].information_schema.tables where table_schema='''+@out_schema+''' and table_name='''+@out_tab+''')'   	
	if @out_temp=0 and @out_server is     null 	  
	  set @sql = N'select @out_exists = count(*) where exists(select * from ['+@out_database+'].information_schema.tables where table_schema='''+@out_schema+''' and table_name='''+@out_tab+''')'   
	if @out_temp=1
	   set @sql = N'select @out_exists = isnull(object_id(''tempdb.'+@out_schema+'.'+@out_tab+'''),0)'   	
	exec sp_executesql @sql, N'@out_exists int OUTPUT', @out_exists=@out_exists OUTPUT
	
	if @out_exists=0 and @out_server is not null
	   raiserror('dynamic table creation on linked server impossible',16,1)
	
	if @out_temp=0 and @out_server is not null 
	  set @sql = N'select @out_identity_col=c.name from ['+@out_server+'].['+@out_database+'].sys.columns c join ['+@out_server+'].['+@out_database+'].sys.objects o on c.object_id=o.object_id join ['+@out_server+'].['+@out_database+'].sys.schemas s on o.schema_id=s.schema_id where o.name='''+@out_tab+''' and s.name='''+@out_schema+''' and c.is_identity=1'   	  
	if @out_temp=0 and @out_server is     null
	  set @sql = N'select @out_identity_col=c.name from ['+@out_database+'].sys.columns c join ['+@out_database+'].sys.objects o on c.object_id=o.object_id join ['+@out_database+'].sys.schemas s on o.schema_id=s.schema_id where o.name='''+@out_tab+''' and s.name='''+@out_schema+''' and c.is_identity=1'   
    if @out_temp=1
	  set @sql = N'select @out_identity_col=c.name from ['+@out_database+'].sys.columns c join ['+@out_database+'].sys.objects o on c.object_id=o.object_id join ['+@out_database+'].sys.schemas s on o.schema_id=s.schema_id where o.object_id=object_id(''tempdb.'+@out_schema+'.'+@out_tab+''') and c.is_identity=1'    
	
	exec sp_executesql @sql, N'@out_identity_col sysname OUTPUT', @out_identity_col=@out_identity_col OUTPUT
	 
	if @out_exists=1 and exists(select * from @tabcols1 where name=@out_identity_col)
	   set @sql_reset_out_identity = 'set identity_insert ['+@out_database+'].['+@out_schema+'].['+@out_tab+'] on;'
		
	if @out_exists=1 and     exists(select * from @exclude_cols_tab where name='mask')	
	  set @insert_clause = 'insert ' + @out_full+'('+@colsextern+')'		    
    if @out_exists=1 and not exists(select * from @exclude_cols_tab where name='mask')
      set @insert_clause = 'insert ' + @out_full+'('+@mask_column+','+@colsextern+')'
	if @out_exists=0	
	  set @insert_clause_into = ' into ' + @out_full 
	
end
 
set @insert_clause     =isnull(@insert_clause,'')
set @insert_clause_into=isnull(@insert_clause_into,'')

declare @mask_extern sysname = ''
declare @order_by nvarchar(4000) = ''

if not exists(select * from @exclude_cols_tab where name='mask')
begin
   select @mask_extern='sum(distinct '+@mask_column+') '+@mask_column+','
   select @order_by = value from @options_tab where value like 'order by%' or value like 'order  by%'
   if @order_by is null or @order_by = ''
     set @order_by = 'order by 2,'+@mask_column
   else
     set @order_by = @order_by+','+@mask_column
end
else
begin
   select @mask_extern=''
   select @order_by = value from @options_tab where value like 'order by%' or value like 'order  by%'
   select @order_by = isnull(@order_by,'')
end

 

if @options like '%use_tempdb%'
begin 

set @sql = 'use ['+@caller_db+'];
'+@sql_reset_out_identity+'
select 1 '+@mask_column+','+@colsintern1+' 
into '+@temptabname2+'
from '+@from1+';

if exists (select * from tempdb.sys.columns where object_id=object_id(''tempdb..''+'''+@temptabname2+''') and is_identity = 1)
 set identity_insert '+@temptabname2+' on;

insert '+@temptabname2+'('+@mask_column+','+@colsextern+')  
select 2 '+@mask_column+', 
'+@colsintern2+'
from '+@from2+';

'+@insert_clause+' 
select '+@mask_extern+@colsextern+'
'+@insert_clause_into+'
from '+@temptabname2+' 
group by '+@colsextern+'
having ' + @op_expr + '
'+@order_by 

end
else
begin	

set @sql = 'use ['+@caller_db+'];
'+@sql_reset_out_identity+'
'+@insert_clause+'
select ' + @mask_extern + @colsextern+'
'+@insert_clause_into+'
from
(
  select 1 '+@mask_column+','+@colsintern1+'
  from '+@from1+'
  union all
  select 2 '+@mask_column+','+@colsintern2+'
  from '+@from2+'
)T
group by '+@colsextern+'
having ' + @op_expr + '
'+@order_by 
end


if @options like '%noexec%'
 select @sql
else
 exec sp_executesql @sql
 
END