Find structural differences between 2 database

[DataMiner]

Hi,
I need to find all the structural differences between 2 copies of a database front-end. I don't need to find differences in the data, but I do need to know of any other differences.

Here's the scenario: About a year ago, I was 90% of the way through converting a large, complex, application over to SQL Server. I had made an offline copy of the whole thing and was working on it. Then I retired, and left it to my successor to finish.

A couple of months ago he died, unexpectedly. (really!). And so did our economy; thus, I am back working my old job, part-time. He never finished the SQL server change, so now, I want to complete it. The off-line development copy still exists and doesn't appear to have changed much. However, there have been a number of changes made to the on-line version. So I need to figure out where all the changes are and meld them into my development version.

I see there is some freeware and shareware out there. Does anyone have any experience with any of these? Or a better idea?

 

[The_Doc_Man]

You might be able to write some code in VBA to enumerate the Tables collection one table at a time for each database, to be opened by creating a database variable and then visiting the DB collections. Open the FieldDefs collection for each table, extract the name, size, type, and any other properties of interest.

Make a set of tables in the database that is doing the comparison. One or more tables that, taken together, show the source DB name, table name, field name, and field properties. If the table names match or if you know the concordance of names for the two DBs, you can do a side-by-side query of fields in corresponding tables for the two DBs. Otherwise, you might try the Documenter and the old tried-and-true eyeball. Which might be far faster if this is a once-and-never-again type of thing.

 

[ACMAIN_CHM]

solution 1. you can use the TOOL->Analyze->Documenter to get the DB structure in word, and then compare these two doc contents.

solution 2. you can use the VBA to get all table structure. here is an example to get the table definition.

Public Sub getTables()
    Dim rs As New ADODB.Recordset
    Dim conn As ADODB.Connection
    Dim sSQL As String
    
    
    Set conn = CurrentProject.Connection
    sSQL = "select id,Name from MSysObjects where Type=1 and flags=0"
    rs.Open sSQL, conn
    Do While Not rs.EOF
        Debug.Print "****"; rs.Fields("Name").Value; "****************"
        getTableColumns rs.Fields("Name").Value
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    Set conn = Nothing
    
End Sub
Public Sub getTableColumns(sTableName As String)
    Dim rs As New ADODB.Recordset
    Dim conn As ADODB.Connection
    Dim fld As ADODB.Field
    Set conn = CurrentProject.Connection
    rs.Open sTableName, conn, , , adCmdTable
    
    For Each fld In rs.Fields
        Debug.Print fld.Name, sFldType(fld.Type), fld.NumericScale, fld.Precision
        
    Next
    
    rs.Close
    Set rs = Nothing
    Set conn = Nothing
End Sub
 
Public Function sFldType(iType As Integer)
    Select Case iType
        Case 20
            sFldType = "BigInt"
        Case 128
            sFldType = "Binary"
        Case 11
            sFldType = "Boolean"
        Case 8
            sFldType = "BSTR"
        Case 136
            sFldType = "Chapter"
        Case 129
            sFldType = "Char"
        Case 6
            sFldType = "Currency"
        Case 7
            sFldType = "Date"
        Case 133
            sFldType = "DBDate"
        Case 134
            sFldType = "DBTime"
        Case 135
            sFldType = "DBTimeStamp"
        Case 14
            sFldType = "Decimal"
        Case 5
            sFldType = "Double"
        Case 0
            sFldType = "Empty"
        Case 10
            sFldType = "Error"
        Case 64
            sFldType = "FileTime"
        Case 72
            sFldType = "GUID"
        Case 9
            sFldType = "IDispatch"
        Case 3
            sFldType = "Integer"
        Case 13
            sFldType = "IUnknown"
        Case 205
            sFldType = "LongVarBinary"
        Case 201
            sFldType = "LongVarChar"
        Case 203
            sFldType = "LongVarWChar"
        Case 131
            sFldType = "Numeric"
        Case 138
            sFldType = "PropVariant"
        Case 4
            sFldType = "Single"
        Case 2
            sFldType = "SmallInt"
        Case 16
            sFldType = "TinyInt"
        Case 21
            sFldType = "UnsignedBigInt"
        Case 19
            sFldType = "UnsignedInt"
        Case 18
            sFldType = "UnsignedSmallInt"
        Case 17
            sFldType = "UnsignedTinyInt"
        Case 132
            sFldType = "UserDefined"
        Case 204
            sFldType = "VarBinary"
        Case 200
            sFldType = "VarChar"
        Case 12
            sFldType = "Variant"
        Case 139
            sFldType = "VarNumeric"
        Case 202
            sFldType = "VarWChar"
        Case 130
            sFldType = "WChar"
        Case Else
            sFldType = "unKnown"
    End Select
End Function

 

[DataMiner]

Thanks for the replies. I have ended up (so far) using a combination of
1. My own code for comparing SQL in queries, table fields, and simple existence of all objects.
2. A commercially available program called AccessDiff for comparing design of forms and reports. Although I have yet to purchase the full version of this program, it looks to be pretty useful and accurate for anyone who might need to track down differences between 2 db's.