Comparison of two tables

Question

Comparison of two tables

Murad_J30

SSC Enthusiast

Points: 160
More actions
June 16, 2003 at 1:03 am

#81783

Hello,
I am writing to all of you to get some infomation regarding comparison of two tables.
The question is:
Are theere any ways to compare data of two tables if the structure are a little different ?
There is a script which does all of these, but the problem is that it does not do data comparison if a structures are different.
Tables that I would like to compare are almost sructurely same, (The Name of columns), but in one of them there have some defference like lenght of filed or Type ...
How can I compare the tables I have mentioned above ?
Thanks,
Murad.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

there's at least 3 scripts in the script library of this site that compares table structures; i used "Compare Tables" in the search engine to find them here, and this is just the link to the one I copied previously for my own use:

http://www.sqlservercentral.com/scripts/contributions/458.asp

lowell@stormrage.com

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Murad_J30 SSC Enthusiast Points: 160 More actions · Answer 2

Thanks for your reply.

I have that script already, bu the problem is that if table structures are different then the script does not compare the data by saying that 'table structure are different ...'

But the tables I would like to compare are almost same , the only difference they have is like defference of lenght in some of fields.

Thanks,

Edited by - murad_j30 on 06/17/2003 02:26:54 AM

stax68 SSChampion Points: 11711 More actions · Answer 3

you can amend the script lowell refers to. At line 302, you will see the following code:

IF EXISTS(SELECT COLUMN_NAME,

DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,

CHARACTER_OCTET_LENGTH,

NUMERIC_PRECISION,

NUMERIC_PRECISION_RADIX,

NUMERIC_SCALE,

DATETIME_PRECISION,

COUNT(*) AS NUMBERS

FROM #TableColumns

GROUP BY COLUMN_NAME,

DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,CHARACTER_OCTET_LENGTH,

NUMERIC_PRECISION,NUMERIC_PRECISION_RADIX,

NUMERIC_SCALE,

DATETIME_PRECISION

HAVING COUNT(*)=1)

If you delete

CHARACTER_MAXIMUM_LENGTH,CHARACTER_OCTET_LENGTH,

from both the select list and the group by clause, you should find that the script will ignore differences in the length of character data columns. If you need to allow other disparities in data types, you can amend this section of the code to allow those, too.

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

stax68 SSChampion Points: 11711 More actions · Answer 4

If you want to compare only using column names, replace the whole section of code with

IF 1=2

Tim

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

Murad_J30 SSC Enthusiast Points: 160 More actions · Answer 5

Murad_J30

SSC Enthusiast

Points: 160

June 19, 2003 at 11:06 pm

#462325

Thanks a lot for your help!

Murad.