Compare SQL Database Schema
Application to show differences between two SQL Server databases.
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.