I need to compare tables on two sql databases for differences with one customer

Question

I need to compare tables on two sql databases for differences with one customer

twdavis-893252

Ten Centuries

Points: 1195
More actions
October 20, 2008 at 2:49 pm

#383152

I need to compare tables on two sql databases for differences with one customer
the first problem is
I am not sure how to select two diffrent datebase with the tsql in a select statement?

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

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

dmc-608719 SSCarpal Tunnel Points: 4663 More actions · Answer 1

To select from another database use a fully qualified name Database.Owner.Table as long as it resides on the same server and your account has access. If its on another server then setup a linked server.

So basically something like

[Code]

select *

from db1.dbo.table

[/code]

Then you cna use inner and outter joins to query and see differences.

Rachel Hawley SSC Veteran Points: 227 More actions · Answer 2

You could try 3rd party products like Red Gate's SQL Compare or Apex SQLDiff. Both have trial periods available for you to test them out in your environment.

Disclaimer - I work for Red Gate 🙂

Rachel.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

Do you mean compare the data or compare the structures?

Rachel is right, Red Gate & Apex both offer great tools for doing either of these operations. I prefer the Red Gate tools and I don't work for them.

However, with work, you can do this yourself. Here's an example that compares structures between two databases using TSQL code: http://www.sqlservercentral.com/scripts/T-SQL+Aids/30564/[/url]

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

krayknot SSCertifiable Points: 7998 More actions · Answer 4

twdavis (10/20/2008)
I need to compare tables on two sql databases for differences with one customer
the first problem is
I am not sure how to select two diffrent datebase with the tsql in a select statement?

This link is helpful for you

http://www.sqlservercentral.com/scripts/T-SQL+Aids/31844/

this link demonstrates the comparison of the two tables in the same database. but if you want to use table(s) in different databases then you will have to link both the Servers and then by prefixing the name of the server before the database table, you can perform the above comparison

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

ljun100 Grasshopper Points: 15 More actions · Answer 5

ljun100

Grasshopper

Points: 15

October 22, 2008 at 9:02 pm

#888445

The 3rd software is the first select

ck9663 Right there with Babe Points: 763 More actions · Answer 6

ck9663

Right there with Babe

Points: 763

October 22, 2008 at 11:13 pm

#888457

What kind of comparison?

Will this help?

Good luck

-- CK

twdavis-893252 Ten Centuries Points: 1195 More actions · Answer 7

I have 3 sql servers DEV, TEST, Production I would like to compare about fifty customer tables between TEST and Production. I just want to see what the difference between the two are. Reason I have a few customers that when they came over from test to the production server they did not come over correctly because they do not function correctly. I would like to see only what has changed from TEST to Production. I may also need this for Products. I would like this in a query for future use.

MrBaseball34 SSCrazy Points: 2515 More actions · Answer 8

I used ApexSQLDiff to do this and it worked great. The DIFF script is fairly verbose

but you can modify it at your desire.

Ulrich Bauhofer Say Hey Kid Points: 700 More actions · Answer 9

If you want to see the differences in the content of two tables, you can try this

Select * from table1

except

Select * from table2

Both tables must have the same structure

twdavis-893252 Ten Centuries Points: 1195 More actions · Answer 10

twdavis-893252

Ten Centuries

Points: 1195

October 27, 2008 at 7:30 am

#889967

Thank you that works perfect.

twdavis-893252 Ten Centuries Points: 1195 More actions · Answer 11

Is there a wild card to use for the cust_table name. something like cust_% ?

SELECT * HMDEV.dbo.cust_table

except

SELECT * HMTST.dbo.cust_table

twdavis-893252 Ten Centuries Points: 1195 More actions · Answer 12

is there away to mark the rows with the table name that is different?

Simon_L SSCarpal Tunnel Points: 4669 More actions · Answer 13

I must admit for either schema or data compares between databases I always go for the redgate suite..

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

I work for Red Gate, so you know. I'll say that there's no way you could write anything that works as well or reliably as their compare products in anywhere near a reasonable amount of time. If you have finding yourself comparing tables or schemas often and trying to alter scripts, you're really being inefficient.

That being said, I never used their products when I was a DBA, but I also knew me schemas inside and out and had very tight, perhaps too tight, control over things. As I look back, I realize that I should have investigated them a bit to see if they would help me.