Compare SQL Database Schema

• Download source code - 43.2 KB

Introduction

As a software product evolves, it is inevitable that the underlying database schema changes, and you have to make sure any changes you make in the development cycle are made to all other database as the software is distributed. In all our products, we include a database management module that propagates changes out to live databases and ensures the version of the software matches the database schema.

In the early stages of development though, the schema changes made by designers and developers come fast and furious, and it can be difficult keeping track of them and propagating them to other databases. At Paritor, we have this problem, and find it can be a long winded job comparing databases manually. There are tools out in the marketplace to analyse databases, show differences, and even script any necessary changes, but apart from the fact that they all cost money, they all seem to be a "Sledge Hammer to Crack a Walnut". I, therefore, sat down the other morning, and put together a simple utility to open up two database, analyse the differences, and report on what it finds.

Background

I wrote it as a WPF application, mainly because I wanted to use the WPF Document namespace to create the report and show it using the WPF Document Viewer. You could easily write it using Windows Forms and use System.Drawing.Print, or even produce a Crystal Report.

Using the code

The program requests details of the two databases to compare, and opens up a SQL connection to each.

Private Sub btnAnalyse_Click(ByVal sender As System.Object, _
        ByVal e As System.Windows.RoutedEventArgs) Handles btnAnalyse.Click
'
' Try and open database 1
'
    Try
        Me.lblProgress.Content = "Open " & Me.txtServer1.Text & _
                                 " " & Me.txtDatabase1.Text
        Dim cnnString As String = "Initial Catalog=" & Me.txtDatabase1.Text & _
                                  ";Connect Timeout=60;Data Source=" & _
                                  Me.txtServer1.Text & ";user id=" & _
                                  Me.txtSQLLogin1.Text & _
                                  ";password=" & Me.txtSQLPassword1.Text
        cnn1 = New SqlConnection(cnnString)
        cnn1.Open()
        Catch ex As Exception
        MessageBox.Show("Connection to Database 1 Failed - " & _
                        ex.Message, "SQL Connection", _
                        MessageBoxButton.OK, MessageBoxImage.Error)
        Exit Sub
    End Try

'
' Try and open database 2
'
    Try
        Me.lblProgress.Content = "Open " & Me.txtServer2.Text & _
                                 " " & Me.txtDatabase2.Text
        Dim cnnString As String = "Initial Catalog=" & Me.txtDatabase2.Text & _
                                  ";Connect Timeout=60;Data Source=" & _
                                  Me.txtServer2.Text & ";user id=" & _
                                  Me.txtSQLLogin2.Text & ";password=" & _
                                  Me.txtSQLPassword2.Text
        cnn2 = New SqlConnection(cnnString)
        cnn2.Open()
        Catch ex As Exception
        MessageBox.Show("Connection to Database 2 Failed - " & ex.Message, _
                        "SQL Connection", MessageBoxButton.OK, _
                        MessageBoxImage.Error)
        cnn1.Close() ' Close each connection
        Exit Sub
    End Try
    Me.lblProgress.Content = ""

    Analyse     ' Call Analyse method differences between databases

    Try
        cnn1.Close() ' Close each connection
        cnn2.Close()
        Catch ex As Exception
        MessageBox.Show("Connection to Database 2 Failed - " & _
                        ex.Message, "SQL Connection", _
                        MessageBoxButton.OK, MessageBoxImage.Error)
    End Try

End Sub

It then creates two lists containing the user table names in each database. The following SQL returns the ID and name of each table:

select id,Name from sysobjects where xType='U'

It then makes a pass through each list, checking for an entry in the other list. This lets us know where we have a table in one database but not in the other. I created a couple of private classes to hold the details of the issues it finds with each table and declare a dictionary list of these.

Private AnalyseTables As Dictionary(Of String, AnalyseTable)

#Region "Private Classes" 
  Private Class AnalyseTable
    Friend ExistsInDatabase1 As Boolean
    Friend ExistsInDatabase2 As Boolean
    Friend Database1ID As Integer
    Friend Database2ID As Integer
    Friend AnalyseColumns As New Dictionary(Of String, AnalyseColumn)

    Friend Sub New(ByVal Database1ID As Integer, ByVal Database2ID As Integer, _
                   ByVal ExistsInDatabase1 As Boolean, _
                   ByVal ExistsInDatabase2 As Boolean)
        Me.Database1ID = Database1ID
        Me.Database2ID = Database2ID
        Me.ExistsInDatabase1 = ExistsInDatabase1
        Me.ExistsInDatabase2 = ExistsInDatabase2
    End Sub
  End Class

  Private Class AnalyseColumn
    Friend Difference As String
 
    Friend Sub New(ByVal Difference As String)
    Me.Difference = Difference
    End Sub
  End Class
 
#End Region

As you can see, there is a Dictionary list of the class AnalysisTable which holds the details of each table, including a dictionary list of the AnalysisColumn class which holds the details of each column issue.

Private Sub CheckTables()
'
' Pass through each table in Database 1 and look to see if it exists in
' Database 2. Then pass through each table in database 2 and check it exists
' in Database 1.
'
' Create an entry in the collection of tables for each unique table
    Dim ds1 As New DataSet
    Dim ds2 As New DataSet
'
' Get list of user tables from database 1
'
    Try
    Dim cmd1 As New SqlCommand("select id, Name from" & _
                    " sysobjects where xType='U'", cnn1)
    Dim da1 As New SqlDataAdapter(cmd1)
    da1.Fill(ds1)
    Catch ex As Exception
    MessageBox.Show("Reading tables from Database 1 Failed - " & _
                    ex.Message, "SQL Connection", _
                    MessageBoxButton.OK, MessageBoxImage.Error)
    Exit Sub
    End Try
'
' Get list of user tables from database 2
'
    Try
        Dim cmd2 As New SqlCommand("select id,Name from " & _ 
                        "sysobjects where xType='U'", cnn2)
        Dim da2 As New SqlDataAdapter(cmd2)
        da2.Fill(ds2)
        Catch ex As Exception
        MessageBox.Show("Reading tables from Database 2 Failed - " & _
                        ex.Message, "SQL Connection", _
                        MessageBoxButton.OK, MessageBoxImage.Error)
        Exit Sub
    End Try
'
' For each table in database1 look to see if it exists in database 2
' and add the result to the tables collection
'
    For Each dr1 As DataRow In ds1.Tables(0).Rows
    Dim ExistsInDatabase2 As Boolean = False
    Dim Database2ID As Integer = 0
    For Each dr2 As DataRow In ds2.Tables(0).Rows
        If dr2("Name") = dr1("Name") Then
            ExistsInDatabase2 = True
            Database2ID = dr2("ID")
            Exit For
        End If
    Next
    AnalyseTables.Add(dr1("Name"), _
                      New AnalyseTable(dr1("ID"), _
                      Database2ID, True, ExistsInDatabase2))
    Next
'
' For each table in database2 look to see if it exists in the tables collection
' If it dosn't we need to add an item for this table to the tables collection
'
    For Each dr2 As DataRow In ds2.Tables(0).Rows
    If AnalyseTables.ContainsKey(dr2("Name")) = False Then
        AnalyseTables.Add(dr2("Name"), New AnalyseTable(0, dr2("ID"), False, True))
    End If
    Next
End Sub

Once the analysis has completed checking for missing tables, it then looks though the AnalysisTable collection, and where it is recorded the table exists in both databases, it builds a list of the table columns. The following SQL does this:

select name,xtype,length from syscolumns where id={the id of the table}

It performs a similar task on the column lists as it did with the table lists, i.e., it checks to see if the columns exists in each list, and where they do, it looks to see that the data type and length are the same.

Private Sub CheckColumns()
'
' Where the tables exists in both databases
' we need to compare the fields in each
'
'
' Pass through each table
'
For Each TableName As String In AnalyseTables.Keys
'
' Look to see if the table exists in both databases and if so
'
    Dim ds1 As New DataSet
    Dim ds2 As New DataSet
    If AnalyseTables(TableName).ExistsInDatabase1 = True And _
       AnalyseTables(TableName).ExistsInDatabase2 = True Then
'
' Get list of columns for the table from database 1
'
      Try
    Dim cmd1 As New SqlCommand("select name,xtype,length from " & _ 
             "syscolumns where id=" & _
             AnalyseTables(TableName).Database1ID, cnn1)
    Dim da1 As New SqlDataAdapter(cmd1)
    da1.Fill(ds1)
      Catch ex As Exception
    MessageBox.Show("Reading table columns from Database 1 Failed - " & _
                    ex.Message, "SQL Connection", _
                    MessageBoxButton.OK, MessageBoxImage.Error)
    Exit Sub
      End Try
'
' Get list of columns for table from database 2
'
      Try
    Dim cmd2 As New SqlCommand("select name,xtype,length from syscolumns" & _ 
             " where id=" & AnalyseTables(TableName).Database2ID, cnn2)
    Dim da2 As New SqlDataAdapter(cmd2)
    da2.Fill(ds2)
      Catch ex As Exception
    MessageBox.Show("Reading table columns from Database 2 Failed - " & _
                    ex.Message, "SQL Connection", _
                    MessageBoxButton.OK, MessageBoxImage.Error)
    Exit Sub
      End Try
'
' For each column in database1 table look to see if it exists in database 2 table
' and add the result to the tables columns collection collection
'
      For Each dr1 As DataRow In ds1.Tables(0).Rows
        Dim Difference As String = ""
        Dim ExistsInDatabase2 As Boolean = False
        For Each dr2 As DataRow In ds2.Tables(0).Rows
           If dr2("Name") = dr1("Name") Then
              If dr2("xtype") <> dr1("xtype") Then
                  Difference = "Type is Different - Database 1 has type of " & _
                               dr1("xtype") & _
                               " Database 2 has type of " & dr2("xtype")
              End If
              If dr2("length") <> dr1("length") Then
                  Difference = "Length is Different - Database 1 has length of " & _
                               dr1("length") & _
                               " Database 2 has length of " & dr2("length")
              End If
              ExistsInDatabase2 = True
              Exit For
            End If
        Next
        If ExistsInDatabase2 = False Then
          Difference = "Does not exists in Database 2"
        End If
        If Difference <> "" Then
          AnalyseTables(TableName).AnalyseColumns.Add(dr1("Name"), _
                        New AnalyseColumn(Difference))
        End If
    Next
'
' For each column in database2 table look to see if it exists in database 1 table
' If it doesn't we need to add it to the tables columns collection
'
      For Each dr2 As DataRow In ds2.Tables(0).Rows
        Dim ExistsInDatabase1 As Boolean = False
        For Each dr1 As DataRow In ds1.Tables(0).Rows
          If dr2("Name") = dr1("Name") Then
            ExistsInDatabase1 = True
            Exit For
          End If
        Next    
        If ExistsInDatabase1 = False Then
          AnalyseTables(TableName).AnalyseColumns.Add(dr2("Name"), _
                    New AnalyseColumn("Does not exist in Database 1"))
        End If
      Next
  End If
Next
End Sub

After completing the analysis, it generates a report. It first creates a flow document and writes the details of all the differences found. When complete, it converts the flow document into a XPS Fixed Page document so that it can add a header and footer.

Private Sub GenerateReport()
    '
    ' Produce a Flow Document containing info on the differences found
    '
    Dim MemStream As New System.IO.MemoryStream
    Dim xpsPackage As Package = Package.Open(MemStream, FileMode.CreateNew)
    Dim FlowDocument As New FlowDocument
    Dim Section As New Section
    Dim Para As Paragraph
    '
    ' Show the databases we have analysed
    '
    Para = New Paragraph
    Section.Blocks.Add(Para)
    Para.FontSize = 12
    Para.Inlines.Add("Paritor Database Compare results for the following databases.")
    Para = New Paragraph
    Section.Blocks.Add(Para)
    Para.FontSize = 12
    Para.Inlines.Add("Database 1:")
    Dim Table As Table
    Dim currentRow As TableRow
    Table = New Table
    Table.Columns.Add(New TableColumn)
    Table.Columns.Add(New TableColumn)
    Table.Columns(0).Width = New GridLength(50)
    Table.FontSize = 10
    Table.RowGroups.Add(New TableRowGroup())
    currentRow = New TableRow()
    Table.RowGroups(0).Rows.Add(currentRow)
    currentRow.Cells.Add(New TableCell(New Paragraph(New Run("Server"))))
    currentRow.Cells.Add(New TableCell(New Paragraph(New Run(Me.txtServer1.Text))))
    currentRow = New TableRow()
    Table.RowGroups(0).Rows.Add(currentRow)
    currentRow.Cells.Add(New TableCell(New Paragraph(New Run("Database"))))
    currentRow.Cells.Add(New TableCell(New Paragraph(New Run(Me.txtDatabase1.Text))))
    Section.Blocks.Add(Table)
    Para = New Paragraph
    Section.Blocks.Add(Para)
    Para.FontSize = 12
    Para.Inlines.Add("Database 2:")
    Table = New Table
    Table.Columns.Add(New TableColumn)
    Table.Columns.Add(New TableColumn)
    Table.Columns(0).Width = New GridLength(50)
    Table.FontSize = 10
    Table.RowGroups.Add(New TableRowGroup())
    currentRow = New TableRow()
    Table.RowGroups(0).Rows.Add(currentRow)
    currentRow.Cells.Add(New TableCell(New Paragraph(New Run("Server"))))
    currentRow.Cells.Add(New TableCell(New Paragraph(New Run(Me.txtServer2.Text))))
    currentRow = New TableRow()
    Table.RowGroups(0).Rows.Add(currentRow)
    currentRow.Cells.Add(New TableCell(New Paragraph(New Run("Database"))))
    currentRow.Cells.Add(New TableCell(New Paragraph(New Run(Me.txtDatabase2.Text))))
    Section.Blocks.Add(Table)
    Para = New Paragraph
    Section.Blocks.Add(Para)
    Para.FontSize = 12
    Para.Inlines.Add("The following tables produced differences")
    '
    ' Pass through the table collection and print details of the differences
    '
    Dim ChangesExists As Boolean = False
    For Each TableName As String In AnalyseTables.Keys
        Dim AnalyseTable As AnalyseTable = AnalyseTables(TableName)
        If AnalyseTable.ExistsInDatabase1 <> _
           AnalyseTable.ExistsInDatabase2 Or AnalyseTable.AnalyseColumns.Count Then
            ChangesExists = True
            Para = New Paragraph
            Section.Blocks.Add(Para)
            Para.FontSize = 14
            Para.Inlines.Add(TableName)
            If AnalyseTable.ExistsInDatabase1 = False Then
                Para = New Paragraph
                Para.FontSize = 10
                Para.Foreground = Brushes.DarkBlue
                Section.Blocks.Add(Para)
                Para.Inlines.Add(" " & _
                             "This table does not exits in database 1")
            End If
            If AnalyseTable.ExistsInDatabase2 = False Then
                Para = New Paragraph
                Para.FontSize = 10
                Para.Foreground = Brushes.DarkBlue
                Section.Blocks.Add(Para)
                Para.Inlines.Add(" " & _
                     "This table does not exits in database 2")
            End If
            For Each ColumnName As String In AnalyseTable.AnalyseColumns.Keys
                Para = New Paragraph
                Section.Blocks.Add(Para)
                Para.FontSize = 10
                Para.Foreground = Brushes.Maroon
                Para.Inlines.Add(" " & ColumnName & " " & _
                   AnalyseTable.AnalyseColumns(ColumnName).Difference)
            Next
        End If
    Next
    If ChangesExists = False Then
        Para = New Paragraph
        Section.Blocks.Add(Para)
        Para.FontSize = 12
        Para.Inlines.Add("No defferences found")
    End If
    FlowDocument.Blocks.Add(Section)
    '
    ' Convert Flowdocument to Fixed Page
    '
    Dim xpsDocument As New XpsDocument(xpsPackage, _
                    CompressionOption.Maximum)
    Dim paginator As DocumentPaginator = _
        CType(FlowDocument, IDocumentPaginatorSource).DocumentPaginator
    Dim rsm As New XpsSerializationManager(New _
                   XpsPackagingPolicy(xpsDocument), False)
    paginator = New DocumentPaginatorWrapper(paginator, _
                New Size(768, 1056), New Size(48, 48))
    rsm.SaveAsXaml(paginator)
    xpsDocument.Close()
    xpsPackage.Close()
    Dim DisplayReport As New DisplayReport
    DisplayReport.OpenStream(MemStream)
    DisplayReport.Show()
End Sub

Finally, it calls a second form that contains a WPF Document Viewer control and passes it the document to display.

History

• 16 Jan. 2008 - Initial version.