Click here to Skip to main content
15,887,917 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi I have two tables in the same database .both the tables have same number columns and data.but how can I find only mismatched data columns data in both the tables.




Thanks in advance
Posted

SQL
declare @mtbname1 as varchar(50)
declare @mtbname2 as varchar(50)


set @mtbname1  = 'tablename1'
set @mtbname2  = 'tablename2'



select * from (select tb.name , colum.name col  from sys.tables tb
inner join 
sys.all_columns colum on 
tb.object_id = colum.object_id  
where tb.name = @mtbname1 )tb1
left outer join   
(select tb.name , colum.name col from sys.tables tb
inner join 
sys.all_columns colum on 
tb.object_id = colum.object_id  
where tb.name = @mtbname2)tb2 
on tb1.col = tb2.col 
where tb2.col is null 

union 


select * from (select tb.name , colum.name col  from sys.tables tb
inner join 
sys.all_columns colum on 
tb.object_id = colum.object_id  
where tb.name = @mtbname1 )tb1
right outer join   
(select tb.name , colum.name col from sys.tables tb
inner join 
sys.all_columns colum on 
tb.object_id = colum.object_id  
where tb.name = @mtbname2)tb2 
on tb1.col = tb2.col 
where tb1.col is null
 
Share this answer
 
v2
select * from table1 except (select * from table2)
Union
select * from table2 except (select * from table1)
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900