I Have Database A and Database B on same server,on these 2 databases table names are different but schema is same for both databases.How can i map or Compare between table names in the A database and table names in B
asked Feb 16, 2011 at 06:26 PM in Default
answered Feb 16, 2011 at 07:23 PM
Do you want to compare the data within the tables, or the actual structure of the tables, except table name?
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):
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.
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.
Open SSMS and run something like the following:
Now run your schema compare tool, comparing
You could also do this by beginning a transaction before the first
A few of the professional tools you can use for the compare (both data and schema) are listed in [this question].: http://ask.sqlservercentral.com/questions/27188/redgate-developer-bundle-verses-idera-sql-toolbox
answered Feb 16, 2011 at 08:12 PM
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.
answered Feb 16, 2011 at 11:42 PM