Compare databases & TABLES

Question

Compare databases & TABLES

Niyala

SSCrazy

Points: 2778
More actions
September 11, 2008 at 4:03 am

#123325

Hi List,
I have a question in relation to comparing two databases.
I have DB1 and DB2. We recently migrated DB1 to DB2 and would like to check if DB1 and DB2 are identical. Especially, we would like to figure out if each corresponding tables in DB1 and DB2 have similar in contents and whether or not they are identical. Is there a faster way to loop through some 50 tables in DB1 and DB2 and compare the two for similarity?
Thanks indeed for your hint on this.
Niyala

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

homebrew01 SSC Guru Points: 55548 More actions · Answer 1

Do you want to know if the data in the tables is the same ? Or if the table schemas are the same ?? I wrote some scripts to compare schemas.

Niyala SSCrazy Points: 2778 More actions · Answer 2

Thanks indeed for your response. In fact my interest is to know whether or not the data in Tables (DB1) and Tables(DB2) were the same. Both in DB1 and DB2, I have Tables that are supposed to store similar data. I expect that they should be identical and that is what I after.

Thanks for your hint

Niyala

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

You could code something like this:

[font="Courier New"]SELECT MIN(Table_1) as Table_1, Fld1, fld2, fld3

FROM

(SELECT 'Table_1' as Table_1, a.Fld1, a.fld2, a.fld3

FROM Table_1 a

UNION ALL

SELECT 'Table_2' as Table_2, b.Fld1, b.fld2, b.fld3

FROM Table_2 b

) tmp

GROUP BY Fld1, fld2, fld3

HAVING COUNT(*) = 1

ORDER BY fld1[/font]

Niyala SSCrazy Points: 2778 More actions · Answer 4

Thanks a lot! The interesting issue that the two databases are not on the same instance. DB1 and DB2 are in separate instances.

Niyala

Big Data Man SSCrazy Eights Points: 8536 More actions · Answer 5

Hey,

Have you considered products such as RedGate Compare for both schema and data comparisons?

http://www.redgate.com

Thanks,

Phillip Cox

homebrew01 SSC Guru Points: 55548 More actions · Answer 6

Niyala (9/11/2008)
Thanks a lot! The interesting issue that the two databases are not on the same instance. DB1 and DB2 are in separate instances.
Niyala

Then you would need linked servers to run the code.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 7

There is no way you can get any level of code done that you're sure does this for US$395. Even at the cheapest rates you'd use way more than that amount in time for something that would be custom, would need to be rewritten, and wouldn't be exhaustively tested.

I work for Red Gate, but their tool (or the one from ApexSQL) do this and they're well worth the money.

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 8

As per suggestion try the evaluation version of ApexSQL Diff tool.

MJ

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

Now back the migration pony up just a minute, please! How did you do the migration? It's important to know because some methods absolutely guarantee that the databases, all database objects, and all of the data is absolutely identical and requires no further checking. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jack Corbett SSC Guru Points: 184394 More actions · Answer 10

SQL Server also ships with a tablediff utility that was really included to check replication, but can be used to compare data across DB's. Here's link to my blog post that includes some information about the utility and also has a link to another blog with more information: http://wiseman-wiseguy.blogspot.com/2008/07/ssis-webinars-and-tablediff.html

Oh, in a previous position I also used the RedGate tools. I have tested the Apex tools as well. I prefer RedGate's.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 11

Jack Corbett (9/23/2008)
SQL Server also ships with a tablediff utility that was really included to check replication...

Now, that's gonna be handy. Didn't know about that. Thanks, Jack! 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jack Corbett SSC Guru Points: 184394 More actions · Answer 12

Glad some of the stuff I read about and give a whirl can be helpful. Every time I think I know what you need to know about SQL Server something new pops up.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jerry Hung SSChampion Points: 12968 More actions · Answer 13

Thankfully I have RedGate tools

SQL Compare - compares schema and objects (view, stored procs, table, etc...)

SQL Data Compare - compares DATA inside tables

http://www.red-gate.com/index3.htm

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

Van Heghe Eddy SSCarpal Tunnel Points: 4449 More actions · Answer 14

Yep me 2,

Working from Development to Test to Production is done in a second with the Redgate Soft,

+ the Sqldoc included comes in handy also..

SQLPrompt is also fine to have but does work difficult with Master.. syntaxes,

+ newly created tables does he not want to recognice,

But for the rest Thumbs up,

Best money ever spended

Wkr,

Eddy