I often come across the need to compare data from 2 tables in SQL Server and see if there is any mismatching data in tables. I know, there are loads of tools available in market for this but the good one I know are all paid tools. The price doesn't look worth it when I know I'll not need the tool throughout the year and i'll need to pay the renew fee again after year end. So I thought of writing a script to dynamically compare the data between any 2 given tables on a server. The script will return the all the mismatching columns along with data. If there are no mismatching columns, it will tell that all data is identical. You may also choose to return only needed number of records after comparision. e.g. if your tables have 2 million records and there are mismatches, then you just want to see first 100 records along with the mismatching columns, then you can specify the @TopNRecords parameter.
The good thing about script is that I have not used any loops in it and comparison is done based on joins. You can specify the joining keys, which normally should be your primary key. But you may choose to use different columns for comparison.
So enjoy the script and leave your comments if you find any bugs.