x

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

ETHMAN5 gravatar image

ETHMAN5
482 44 46 48

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

3 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][4].

[4]: http://www.red-gate.com/products/sql-development/sql-data-compare/
more ▼

answered Feb 16, 2011 at 07:23 PM

Cyborg gravatar image

Cyborg
10.6k 36 40 45

+1 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

Fatherjack gravatar image

Fatherjack ♦♦
42.6k 75 79 108

+1 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?

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
more ▼

answered Feb 16, 2011 at 08:12 PM

KenJ gravatar image

KenJ
19.8k 1 3 11

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
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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x723
x473
x86

asked: Feb 16, 2011 at 06:26 PM

Seen: 1718 times

Last Updated: Feb 16, 2011 at 06:26 PM