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

more ▼

asked Feb 16, 2011 at 06:26 PM in Default

avatar image

482 44 46 51

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?

Feb 17, 2011 at 08:09 PM KenJ
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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.

more ▼

answered Feb 16, 2011 at 07:23 PM

avatar image

10.8k 37 58 51

  • for all your recommendations, I have used DataCompare and Data Inspector and they are very good.

Feb 16, 2011 at 11:52 PM Håkan Winther

Data Compare has been very useful to me a number of times.

Feb 17, 2011 at 09:41 AM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Feb 16, 2011 at 11:42 PM

avatar image

Fatherjack ♦♦
43.8k 79 102 118

  • for the Atlantis love!

Feb 17, 2011 at 12:04 AM WilliamD
(comments are locked)
10|1200 characters needed characters left

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):

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

 -- all the model columns that aren't in msdb
 SELECT * FROM model.sys.columns
 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.


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.

more ▼

answered Feb 16, 2011 at 08:12 PM

avatar image

25k 3 13 20

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.

Feb 16, 2011 at 08:36 PM ETHMAN5

i tried all tools no use.

Feb 16, 2011 at 08:37 PM ETHMAN5

@ETHMAN5 - 'I tried all tools no use'. What does that mean? Not really helping us to help you.

Feb 17, 2011 at 04:03 AM Matt Whitfield ♦♦

also, if all tools had been tried Ethman5 would see that both Atlantis' Tools and Redgate Tools would be up to the job

Feb 17, 2011 at 05:36 AM WilliamD
(comments are locked)
10|1200 characters needed characters left

You can use the Compare Tables/Views option of xSQL Data Compare. With this option you manually choose which tables you want to be compared and synchronized.

DISCLAIMER: I'm affiliated with xSQL.

more ▼

answered May 22, 2017 at 10:37 AM

avatar image

1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 16, 2011 at 06:26 PM

Seen: 2072 times

Last Updated: May 22, 2017 at 10:37 AM

Copyright 2018 Redgate Software. Privacy Policy