How can i do to comparate the differences between tables?

Question

Post reply

How can i do to comparate the differences between tables?

Hector Angello Silva Carmona

SSCrazy

Points: 2720
More actions
January 18, 2005 at 6:23 am

#74841

Hi friends, i need to do a comparation between the tables of the server of Production and the server of development work, i need find the diferences,
for example
Production work                                        Development work
CT_tableX                                               CT_TableX
name varchar (200)                                  name varchar (30)
adress varchar (30)                                   EAdreess       varchar (30)
well the table CT_tableX exists two differences in the area of Production Work and the area of Development work
So to do this comparation i can script out the table and comparate the scripts, but is there another way to do the work easier? for example with a query?
Thanks ........

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

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

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 1

You can use RedGate SQL Compare and SQL Compare Data tools.

Check in http://www.red-gate.com/sql/summary.htm

Also, for a quick data compare, you can use

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM production.db.dbo.CT_TAbleX

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM Development.db.dbo.CT_TAbleX

If the numbers are different, then the values are different.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

BINARY_CHECKSUM() *can* be tricky at times when comparing only strings. Consider this from SQL Server MVP Adam Machanic:

select binary_checksum('a b c')

select binary_checksum('ab c')

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

David Burrows SSC Guru Points: 65142 More actions · Answer 3

I guess by your post that you want to check the table structures not the data. The comparison will depend on column sequence.

select c.*

from sysobjects o

inner join syscolumns c on c.[id] = o.[id]

where o.[name] = 'tablename'

will list the columns in a table.

colid will tell you the sequence of the columns

you could match each table on colid looking for differences (type, length, collation etc) or try matching on name looking for where names match but other details do not and where name not in other table etc etc

Far away is close at hand in the images of elsewhere.
Anon.

Hector Angello Silva Carmona SSCrazy Points: 2720 More actions · Answer 4

yes you are right, thanks but the tricks are very interesting too, I have done a sp that shows if the tables are equal or not, i will send if someone want to ask for it. Thanks

i have used that query to do the comparation....

SELECT * FROM OPENROWSET('SQLOLEDB','MYServer';'dbo';'password'," select so.name,sc.name,st.name ,convert(varchar,sc.length) FROM mybase.dbo.syscolumns sc (nolock),mybase.dbo.systypes st (nolock),mybase.dbo.SysObjects so (nolock) WHERE sc.id = OBJECT_ID ('TableX') and st.xtype = sc.xtype and so.name = 'TableX' and so.type ='U' " )