question

You can use [tablediff][1] utility to compare two tables. Or try [Data Inspector][2] Or [Schema Inspector][3] from Atlantis Interactive it is free. Or Try Red-Gate [Data Compare][4]. [1]: http://msdn.microsoft.com/en-us/library/ms162843.aspx [2]: http://www.atlantis-interactive.co.uk/products/datainspector/default.aspx [3]: http://www.atlantis-interactive.co.uk/products/schemainspector/default.aspx [4]: http://www.red-gate.com/products/sql-development/sql-data-compare/

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][1]. [1]: http://ask.sqlservercentral.com/questions/27188/redgate-developer-bundle-verses-idera-sql-toolbox

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.

You can use the Compare Tables/Views option of [xSQL Data Compare][1]. With this option you manually choose which tables you want to be compared and synchronized. DISCLAIMER: I'm affiliated with xSQL. [1]: http://www.xsql.com/products/sql_server_data_compare/?utm_source=pragmatic&utm_medium=articles&utm_campaign=xsql