Click here to Skip to main content
13,901,026 members
Click here to Skip to main content
Add your own
alternative version


77 bookmarked
Posted 29 Dec 2008
Licenced CPOL

Database Comparator

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


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.


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, AS Col, AS TypeName, 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 = WHERE     (dbo.sysobjects.xtype = 'u
') AND 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.



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:


Future Work 

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


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


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


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

You may also be interested in...

Comments and Discussions

Questiondatabase concurrency comparator Pin
Member 140096935-Oct-18 22:14
memberMember 140096935-Oct-18 22:14 
QuestionPlease add the sql Export option Pin
Member 108968235-May-15 22:25
memberMember 108968235-May-15 22:25 
Questiongood Pin
cbysc6-May-13 16:03
membercbysc6-May-13 16:03 
QuestionThanks Pin
reypotxo6-Jun-12 20:57
memberreypotxo6-Jun-12 20:57 
GeneralGood work. Simple tool just what lots of us need. Pin
ronp18-Jan-09 0:02
memberronp18-Jan-09 0:02 
GeneralRe: Good work. Simple tool just what lots of us need. Pin
Amir Mehrabi-Jorshari18-Jan-09 22:00
memberAmir Mehrabi-Jorshari18-Jan-09 22:00 
GeneralGood Work - but Pin
Charles K. Kincaid5-Jan-09 6:34
memberCharles K. Kincaid5-Jan-09 6:34 
GeneralRe: Good Work - but Pin
bgs2645-Jan-09 22:29
memberbgs2645-Jan-09 22:29 
NewsRe: Good Work - but Pin
Amir Mehrabi-Jorshari8-Jan-09 4:40
memberAmir Mehrabi-Jorshari8-Jan-09 4:40 
NewsRe: Good Work - but Pin
Amir Mehrabi-Jorshari8-Jan-09 6:12
memberAmir Mehrabi-Jorshari8-Jan-09 6:12 
GeneralCheck out OpenDBDiff Pin
grundt30-Dec-08 12:49
membergrundt30-Dec-08 12:49 
GeneralRe: Check out OpenDBDiff Pin
Amir Mehrabi-Jorshari30-Dec-08 18:44
memberAmir Mehrabi-Jorshari30-Dec-08 18:44 
GeneralRe: Check out OpenDBDiff Pin
glittle24-Jan-09 10:29
memberglittle24-Jan-09 10:29 
AnswerRe: Check out OpenDBDiff Pin
Amir Mehrabi-Jorshari4-Jan-09 23:43
memberAmir Mehrabi-Jorshari4-Jan-09 23:43 
GeneralRe: Check out OpenDBDiff Pin
ronp17-Jan-09 23:59
memberronp17-Jan-09 23:59 
GeneralRe: Check out OpenDBDiff Pin
dojohansen20-Jul-09 0:31
memberdojohansen20-Jul-09 0:31 
GeneralGood but needs lot of improvements Pin
Kant29-Dec-08 7:41
memberKant29-Dec-08 7:41 
GeneralRe: Good but needs lot of improvements Pin
Amir Mehrabi-Jorshari29-Dec-08 18:34
memberAmir Mehrabi-Jorshari29-Dec-08 18:34 
GeneralMy vote of 2 Pin
Srinath Gopinath29-Dec-08 3:39
memberSrinath Gopinath29-Dec-08 3:39 
GeneralRe: My vote of 2 Pin
Amir Mehrabi-Jorshari29-Dec-08 18:37
memberAmir Mehrabi-Jorshari29-Dec-08 18:37 
Generalyour idea is good but... Pin
Guillaume Leparmentier29-Dec-08 3:36
memberGuillaume Leparmentier29-Dec-08 3:36 
GeneralRe: your idea is good but... Pin
Amir Mehrabi-Jorshari29-Dec-08 18:39
memberAmir Mehrabi-Jorshari29-Dec-08 18:39 
GeneralMy vote of 1 Pin
Paul E. Bible29-Dec-08 3:32
memberPaul E. Bible29-Dec-08 3:32 
GeneralRe: My vote of 1 Pin
Amir Mehrabi-Jorshari29-Dec-08 3:34
memberAmir Mehrabi-Jorshari29-Dec-08 3:34 
GeneralRe: My vote of 1 Pin
jszczur6-Jan-09 22:23
memberjszczur6-Jan-09 22:23 

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

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

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web05 | 2.8.190306.1 | Last Updated 8 Jan 2009
Article Copyright 2008 by Amir Mehrabi-Jorshari
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid