question

ETHMAN5 avatar image
ETHMAN5 asked

How to compare database tables

Hi All 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
sqlsql-server-2000server
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I'm thinking I may have misunderstood the question. Maybe you're not trying to do a data compare. Does each of the 699 tables in database B correspond to exactly one table in database A with each of those paired tables having different names but the same columns? If so, are you just trying to get a list of paired table names?
0 Likes 0 ·
Cyborg avatar image
Cyborg answered
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/
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 for all your recommendations, I have used DataCompare and Data Inspector and they are very good.
0 Likes 0 ·
Data Compare has been very useful to me a number of times.
0 Likes 0 ·
KenJ avatar image
KenJ answered
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
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@ETHMAN5 - 'I tried all tools no use'. What does that mean? Not really helping us to help you.
1 Like 1 ·
also, if all tools had been tried Ethman5 would see that both Atlantis' Tools and Redgate Tools would be up to the job
1 Like 1 ·
Thank you for your sug but i have 700 tables in database A and 659 tables in database B not possible to compare with tables names.only option is to compare with column names in each table.
0 Likes 0 ·
i tried all tools no use.
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 for the Atlantis love!
0 Likes 0 ·
endizhupani avatar image
endizhupani answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.