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:
- Master section
- 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.
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.
History
- 29th December, 2008: Initial post
- 8th Jan, 2009 : Version 1.1 Released
