Click here to Skip to main content
Click here to Skip to main content

Database Comparator

, 8 Jan 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
With this tool, you can compare two databases and generate a difference report.

Introduction 

Nowadays, Microsoft SQL Server is one of the most popular DBMS in the world and most companies and software engineers use it as a database. During development and maintenance of a program, it is unclear whether a database has been changed. For example, when you install your program on the customer's site, after some time if you want to upgrade this software / database, you must have more significant data because if you can't upgrade the database properly, then your program doesn't work properly. For solving this problem, I think that if there is a tool that can compare the main database (on the site of the developer) with the slave database (on the customer site) and find the difference between them, it can help us to maintain a program properly.

Design

For design and implementation of this tool, I used C# .NET and specified my tool to Microsoft SQL Server 2000. The user interface of my program has 2 sections:

  1. Master section 
  2. Slave section

In the Master section, the program retrieves information from the main site on developer side and in the Slave section, it retrieves information from the customer site. Information that is retrieved from the databases involves Table List, Table Names, Column Names, Column Types, etc. For the first step for retrieving Tables List, in the SQL Server 2000, all information about tables and views is stored in a table with name "sysobjects". For retrieving information from this table, I use a query as follows:

SELECT     name AS TableName, id FROM         dbo.sysobjects WHERE     (xtype = 'u')

After getting a list of tables in the two databases, now I can compare the list of tables and find out which one of the tables is a new table. Each table that exists in the master list and does not exist in the slave list is a new table. But for comparing two databases, this is not enough because may be some table's columns have been changed and by this method, we can't find these changes. For completing my method, I must compare each table one by one and retrieve information of each table. For retrieving information of tables, I use a query as follows:

SELECT     dbo.syscolumns.id, dbo.syscolumns.name AS Col, dbo.systypes.name AS TypeName,
dbo.sysobjects.name AS TableName, dbo.syscolumns.length AS Len FROM         dbo.syscolumn
s INNER JOIN dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xusertype INNER JOIN dbo
.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.id WHERE     (dbo.sysobjects.xtype = 'u
') AND dbo.sysobjects.name like  [TABLENAME] 

After retrieving information such as column name, column type and column size, now I can compare and find a better result.

User Interface

For using my program, on the left side of my program you can see the master section and on the right side you see the slave section. For each section you must type server name, user id and password for connecting. After connecting to the databases, now you must just click on compare! 

For better virtual comparing, I use a tree for each database. At the first level of tree, you can see the list of tables and by expanding each node, you can see its information such as column name, columns type, etc.

SQL1.JPG

SQL2.JPG

In this version of my program, the software just displays the difference but for future work, I want to use this information software to produce a SQL script file that can save into a file and customer can execute this file for upgrading his/her database.

Next Version (1.1) 

In this version , You can save the schema of your database into a file (*.schema)
and in the later you can fetch the schema of your database offline.
in the next picture you can see what ever you expected:

SQL3.JPG

Future Work 

In future, this may be able to generate a SQL report.  

History 

  • 29th December, 2008: Initial post 
  • 8th Jan, 2009 : Version 1.1 Released

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Amir Mehrabi-Jorshari
Software Developer (Senior) Omid Farda IT Company
Iran (Islamic Republic Of) Iran (Islamic Republic Of)
Master of Science in Computer Engineering

Comments and Discussions

 
Questiongood Pinmembercbysc6-May-13 17:03 
QuestionThanks Pinmemberreypotxo6-Jun-12 21:57 
GeneralGood work. Simple tool just what lots of us need. Pinmemberronp18-Jan-09 1:02 
GeneralRe: Good work. Simple tool just what lots of us need. PinmemberAmir Mehrabi J.18-Jan-09 23:00 
GeneralGood Work - but PinmemberCharles K. Kincaid5-Jan-09 7:34 
GeneralRe: Good Work - but Pinmemberbgs2645-Jan-09 23:29 
NewsRe: Good Work - but PinmemberAmir Mehrabi J.8-Jan-09 5:40 
NewsRe: Good Work - but PinmemberAmir Mehrabi J.8-Jan-09 7:12 
GeneralCheck out OpenDBDiff Pinmembergrundt30-Dec-08 13:49 
GeneralRe: Check out OpenDBDiff PinmemberAmir Mehrabi J.30-Dec-08 19:44 
GeneralRe: Check out OpenDBDiff Pinmemberglittle24-Jan-09 11:29 
AnswerRe: Check out OpenDBDiff PinmemberAmir Mehrabi J.5-Jan-09 0:43 
GeneralRe: Check out OpenDBDiff Pinmemberronp18-Jan-09 0:59 
GeneralRe: Check out OpenDBDiff Pinmemberdojohansen20-Jul-09 1:31 
GeneralGood but needs lot of improvements PinmemberKant29-Dec-08 8:41 
GeneralRe: Good but needs lot of improvements PinmemberAmir Mehrabi J.29-Dec-08 19:34 
GeneralMy vote of 2 PinmemberSrinath G Nath29-Dec-08 4:39 
GeneralRe: My vote of 2 PinmemberAmir Mehrabi J.29-Dec-08 19:37 
Generalyour idea is good but... PinmemberGuillaume Leparmentier29-Dec-08 4:36 
GeneralRe: your idea is good but... PinmemberAmir Mehrabi J.29-Dec-08 19:39 
GeneralMy vote of 1 PinmemberPaul E. Bible29-Dec-08 4:32 
GeneralRe: My vote of 1 PinmemberAmir Mehrabi J.29-Dec-08 4:34 
GeneralRe: My vote of 1 Pinmemberjszczur6-Jan-09 23:23 
GeneralRe: My vote of 1 Pinmembersupercat98-Jan-09 8:46 
GeneralGood Article! PinmemberMember 443280429-Dec-08 4:21 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.1411023.1 | Last Updated 8 Jan 2009
Article Copyright 2008 by Amir Mehrabi-Jorshari
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid