compare two tables in different environments.

[vanikanc]

Hello, Is there some tool in sql server which would help me compare two tables and tell me what is different in it? Thanks!

[Wendelius]

Do you want to compare the contents of the tables. If that's the case I think the quickest way could be to use EXCEPT[^]. If the table structure is the same, you could do comparison like:

SELECT * FROM FirstTable
EXCEPT
SELECT * FROM SecondTable

and then other way round:

SELECT * FROM SecondTable
EXCEPT
SELECT * FROM FirstTable

If there are structural differences, specify the columns you want to compare.

The need to optimize rises from a bad design.My articles[^]

[Mycroft Holmes]

Are you talking about comparing the structure or the data? They require 2 different strategies. Do you want to do this for 1 pair of table or are you looking to compare an entire database? They require 2 different strategies. If you need to compare databases then look at Red-Gate sql and data compare tools.

Never underestimate the power of human stupidity RAH

[vanikanc]

To be more clear, I would like compare the data with in two tables of same design. Would like to report all rows that are different.

[vanikanc]

Thanks so much, EXCEPT really works, and it is really fast as well.

[Wendelius]

Since you mentioned the EXCEPT solution, consider voting the post (http://www.codeproject.com/Messages/3802207/Re-compare-two-tables-in-different-environments.aspx[^]) good or bad depending how you feel the answer helped you.

The need to optimize rises from a bad design.My articles[^]

[dingoishere]

Try D-Softs Database Compare d-softs.com

[Om Prakash Pant]

Please check the following link which might help you: http://www.sqlteam.com/article/comparing-tables[^]

Om Prakash Pant