Comparing records in SQL 1

[collierd]

Hello

I am using sql 2000

I have systypes (data types table) for a database on one server and systypes for a database of the same name on another
Basically both servers should mirror each other
There are some slight discrepenices (missing User defined datatypes or slight differences in the type of a user defined data type)

How would I compare the 2, displaying only datatypes that differ?

Do I need to compare on every field or is there a quick way to compare the whole record?

Thanks

Damian.

 

[vongrunt]

Here comes long shot... didn't checked, should work:

select * from 
(select name as src_key, checksum(*) as src_checksum from systypes ) A
full outer join 
(select name as target_key, checksum(*) as target_checksum from another_server.database.dbo.systypes ) B
on A.src_key=B.target_key
where src_checksum is null or target_checksum is null or src_checksum <> target_checksum

 

[collierd]

V good
Doesn't quite work in the sense that I created some data types manually and have just realised that the usertype record differs as a result
Will certainly store this for future use though
I think the checksum(*) part is what I was looking for

Thanks

Damian.

 

[maswien]

The SQL looks good to me, it should work in the case you created user types, this difference should be covered by full outer join.