Click here to Skip to main content
15,121,963 members
Articles / Database Development / SQL Server
Posted 10 Oct 2006


132 bookmarked

Database Schema Comparison Utility

Rate me:
Please Sign up or sign in to vote.
4.74/5 (32 votes)
16 Oct 2006CPOL2 min read
An article describing a way to find the differences between two database instances
Sample Image - DatabaseCompare.jpg


When most developers develop database code (including tables, views, stored procedures and functions, they tend to develop in a development environment and then migrate those changes later to other servers (staging, production, whatever). While some of us are good about keeping track of those changes, the majority of us are not.

Red-Gate Software offers a tool, SQL Compare to compare the schema of two databases and show them to the user. This tool is not inexpensive, but it works very well.

This article provides the basic functionality used to do the same thing as SQL Compare, at a fraction of the price.


SQL 2000 and above uses a series of system tables to store the definition of all objects in a database. The important objects (for this project) are:

  • sysobjects - Stores a list of all objects in the database (MSDN Reference)
  • syscolumns - Stores a list of the columns of the objects in the database (MSDN Reference)
  • syscomments - Stores the definition of objects in the database (MSDN Reference)

Using the Code

The code is broken into two projects:

  • DatabaseCompare (the Winforms project)
  • DatabaseCompare.Domain (the database access project)

There is nothing ground breaking in the Winforms project. I did use a couple delegates to make the UI more responsive when performing long tasks.

The Domain project contains classes that actually perform the work in gathering the schema of a database and comparing the two databases.

The code is fairly self explanatory, the main entry points to the Domain code are Database.TestConnection(), Database.LoadObjects(), and Database.CompareTo().

Points of Interest

Longer function/stored procedure/view definitions are maintained in multiple rows in the syscomments database and must be retrieved like so:

using( SqlCommand command = conn.CreateCommand() )
    command.CommandText = "select text from syscomments where id=@id";
    command.Parameters.Add( "@id", this.Id );
    using ( SqlDataReader reader = command.ExecuteReader() )
        while( reader.Read() )
            textDefinition += reader.GetString( 0 ).Trim().ToLower();

This code does not create drop/create scripts for tables, but that is a fairly trivial exercise for you to do if you need that functionality.

Using the sysdepends table, you could determine what the dependencies for the objects are, in case you are creating CREATE scripts and need to know what to create first.


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


About the Author

Sean Goodpasture
Web Developer
United States United States
I'm the owner and principal consultant of a small (one man!) shop here in the Dallas, Texas area.

I mostly work with Microsoft technologies, but run a lot of Linux at home.

Comments and Discussions

GeneralThat's very useful for a rookie like me Pin
Cenkay Vergili22-Sep-13 22:47
MemberCenkay Vergili22-Sep-13 22:47 
SuggestionBuilt in functionality in Visual Studio now Pin
Duder8128-Nov-12 11:44
MemberDuder8128-Nov-12 11:44 
GeneralMy vote of 5 Pin
Kanasz Robert26-Sep-12 5:59
professionalKanasz Robert26-Sep-12 5:59 
Questionproject on datbase schmema Pin
venkatrupa3-Sep-12 6:58
Membervenkatrupa3-Sep-12 6:58 
QuestionNice Work...!!! Pin
adkalavadia4-Jan-12 4:21
Memberadkalavadia4-Jan-12 4:21 
BugApplication show an error while comparing database Pin
dinesh830-Dec-11 21:07
Memberdinesh830-Dec-11 21:07 
GeneralRe: Application show an error while comparing database Pin
Wrangly7-Dec-16 23:03
MemberWrangly7-Dec-16 23:03 
GeneralMy vote of 5 Pin
Midax15-Sep-11 5:15
MemberMidax15-Sep-11 5:15 
GeneralMy vote of 5 Pin
Sunasara Imdadhusen16-Feb-11 1:23
professionalSunasara Imdadhusen16-Feb-11 1:23 
GeneralNice work! Pin
Sunasara Imdadhusen16-Feb-11 1:22
professionalSunasara Imdadhusen16-Feb-11 1:22 
GeneralGreat Work Pin
Chosen32028-Jan-09 10:10
MemberChosen32028-Jan-09 10:10 
GeneralVery good article Pin
Ajith R Nair16-Jan-09 1:56
MemberAjith R Nair16-Jan-09 1:56 
General[Message Removed] Pin
ped2ped29-Aug-08 20:07
Memberped2ped29-Aug-08 20:07 
GeneralFree tool D-softs DB Compare Pin
dingoishere14-Aug-08 16:58
Memberdingoishere14-Aug-08 16:58 
GeneralRe: Free tool D-softs DB Compare Pin
dhay199926-Dec-08 9:09
Memberdhay199926-Dec-08 9:09 
GeneralTwo improvements Pin
Zubair Ahmed28-Jul-08 1:17
MemberZubair Ahmed28-Jul-08 1:17 
GeneralThanks Pin
Gaurang Desai25-Jun-08 11:39
MemberGaurang Desai25-Jun-08 11:39 
GeneralThanks Pin
jawwadalam22-May-08 23:05
Memberjawwadalam22-May-08 23:05 
Generalgetting table info Pin
tundefama22-May-08 4:38
Membertundefama22-May-08 4:38 
GeneralExcellent Job Pin
PeterHicks25-Apr-08 6:00
MemberPeterHicks25-Apr-08 6:00 
GeneralOne nit to pick Pin
blemoine27-Feb-08 14:51
Memberblemoine27-Feb-08 14:51 
Generalcross thread operation not valid!! Pin
mfc_surfer9-Sep-07 3:15
Membermfc_surfer9-Sep-07 3:15 
GeneralRe: cross thread operation not valid!! Pin
Sean Goodpasture9-Sep-07 4:15
MemberSean Goodpasture9-Sep-07 4:15 
GeneralRe: cross thread operation not valid!! Pin
mfc_surfer13-Sep-07 2:01
Membermfc_surfer13-Sep-07 2:01 
GeneralRe: cross thread operation not valid!! Pin
Sean Goodpasture13-Sep-07 3:23
MemberSean Goodpasture13-Sep-07 3: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.