Compare specific tables

beorn107

Can anyone provide some example code (vb.net preferred) or point me to some existing code that shows how to use the toolkit to compare a select list of tables.

For example I have a pubs db on 2 servers and I want to compare just the authors, sales and stores tables. I do not want to do a complete compare of the databases.

Thanks in advance.

Brian Donahue

Hello,

Do you want to comapre schema or data? For schema, it's a matter of changing the .Selected property in the loop that runs through the differences collection. For Data Compare, it's a bit different.

beorn107

Thanks for the quick reply. I just want to compare schema. So how would I use the selected property in the below code to just compare certain database objects? Does using the selected property cause the sql compare engine to compare only the items I want versus all the items in the databases? Some of the databases are quite large and I don't want to compare everything everytime. Thanks!

Dim db1 As Database = New Database
Dim db2 As Database = New Database

db1.Register(New ConnectionProperties("server1", "pubs"), Options.Default)
db2.Register(New ConnectionProperties("server2", "pubs"), Options.Default)

Dim differences As Differences = db1.CompareWith(db2, Options.Default)
Dim difference As Difference

For Each difference In differences
Console.WriteLine("{0} {1} {2}", difference.Type.ToString(), difference.DatabaseObjectType.ToString(), difference.Name)
difference.Selected = True
Next

'dispose
db1.Dispose()
db2.Dispose()

Brian Donahue

You can -- but the savings is mainly in the comparison processing time. The software still needs to gather up all of the schema for the databases first, which is the time-consuming bit.

But, say you have a hashtable with a list of tables that you want to compare:

Dim db1 As Database = New Database 
Dim db2 As Database = New Database 

Dim ht As Hashtable = New Hashtable
ht.Add(0,"[dbo].[table1]")
ht.Add(1,"[dbo].[table2]")

db1.Register(New ConnectionProperties("server1", "pubs"), Options.Default) 
db2.Register(New ConnectionProperties("server2", "pubs"), Options.Default) 

Dim differences As Differences = db1.CompareWith(db2, Options.Default) 
Dim difference As Difference 
Dim sName As String

For Each difference In differences 

   For Each sName In ht
If sName=difference.Name Then
difference.Selected = True 
ELSE: difference.Selected=False
End If
Next
Next 

'dispose 
db1.Dispose() 
db2.Dispose()