How to compare database tables

You can use tablediff utility to compare two tables. Or try Data Inspector Or Schema Inspector from Atlantis Interactive it is free. Or Try Red-Gate Data Compare.

My recommendation would be to use TableDiff if you dont have any other installed as it comes with SQL Server, after that then for $0 the Atlantis Interactive tools are awesome, after that then I'd pick something like RedGate's SQL Compare.

If you want to explore a challenge then you could generate the DDL for the objects you want to compare and save them to individual folders and then point WinMerge at them, thus comparing the tables by their CREATE scripts.

Do you want to compare the data within the tables, or the actual structure of the tables, except table name?

Data

If you want to compare the data, you can use a data compare tool and map the tables to each other. If you don't have a tool handy (most of the tool makers seem to provide free trials), you can always compare them with a SQL script. Here is a quick example that compares the sys.columns table in two databases to give you differing rows (you'll just replace these with your databases and table names):

-- all the msdb columns that aren't in model
SELECT * FROM msdb.sys.columns
EXCEPT
SELECT * FROM model.sys.columns

-- all the model columns that aren't in msdb
SELECT * FROM model.sys.columns
EXCEPT
SELECT * FROM msdb.sys.columns

This query will show you that there are differences, but won't necessarily help to pinpoint them. The HUGE advantage of a commercial tool is that it can identify the particular differences, as well as generate the SQL script to synchronize the tables and do the synchronization for you.

Structure

If you are looking to compare the structures of the tables, I'm not sure which compare tools might allow you to compare the structure of tables with different names while ignoring the names. They seem to be geared towards avoiding this specific situation.

A little sp_rename sleight of hand can get a schema compare tool to do the compare. Use sp_rename to rename one of the tables, compare the tables with the tool (the names now match!), then sp_rename the table back to the original name. This only works when both table names are available in the database where you are executing sp_rename (can't try to end up with two table2 in the same schema!).

Open SSMS and run something like the following:

EXEC DatabaseA.dbo.sp_rename 'table1', 'table2', 'object'

Now run your schema compare tool, comparing DatabaseA.schema.table2 to DatabaseB.schema.table2. When the compare is complete, you hop back into SSMS and and undo the rename...

EXEC DatabaseA.dbo.sp_rename 'table2', 'table1', 'object'

You could also do this by beginning a transaction before the first sp_rename. Then, after the compare, you just run ROLLBACK rather than sp_rename to revert the table to its original name.

Available Tools

A few of the professional tools you can use for the compare (both data and schema) are listed in this question.