Click here to Skip to main content
15,861,172 members
Articles / Database Development / SQL Server
Article

SQL Server Stored Procedures Comparer

Rate me:
Please Sign up or sign in to vote.
4.95/5 (35 votes)
22 Apr 2010CPOL9 min read 125K   6.5K   81   33
Windows application that compares stored procedures between two SQL Server database

Introduction

First and foremost, I'm a developer from Philippines so pardon me for my bad English. I am currently employed in an offshore development company that designs, develops and delivers technology solutions for many pharmaceutical companies. This is my first article for the CodeProject and is entitled "SQL Server Stored Procedures Comparer". The CodeProject community had helped me greatly in many of my programming tasks, so I think it's time that I give back something in return that maybe useful.

As the title implies, this windows application can be a great help for database programmers (btw, I'm actually a database developer) who wants to easily check differences in stored procedures between two physically distinct database. Another reason that I've developed this application was just simply because I can't find any free and opensource application/tool that does the job as most of them are commercial products. Here is a quick Google search to show you what I mean.

This application can check stored procedures that are new, changed, and removed between two database. The application was designed also to be user friendly by making it responsive (using asynchronous pattern on long running tasks), and displaying the result of comparison in a list view with a simple "winmerge-like" compare display.

Background

Two of the most useful concepts used in this article are asynchronous pattern and event-driven programming. Also, this article was greatly inspired by Generic, Reusable Diff Algorithm in C# - II By Michael Potter as used in displaying the compare result in a "winmerge-like" fashion. I also want to add that this application does not call any stored procedures on the databases. It simply generates a tsql query on the fly, that selects the necessary information in the Information_Schema and some system views. By doing so, the application does not requires any installation of specific store procedures/functions on the databases to work.

Using the Application

design.jpg

The application is simple. It just only requires that you have permission to access the stored procedures on the two database that you want to compare. You can use either Windows authentication or SQL Server Authentication when connecting.

pic1.jpg

  1. We start by entering the Source and Target machines and choosing the appropriate authentication required. Pressing the Connect button will initiate an asynchronous database connection to whatever sql instance is available in the source/target machine. That connection will then be used to query the available databases and list it in the comboboxes.

    pic2.jpg

  2. Then choose the Source and Target databases that will be used in the compare. Then press the Compare button in the toolbar.

    pic4.jpg

  3. Starting the compare will initiate the asynchronous operation of the ProcedureComparerManager class that retrieves all the stored procedures from both database and comparing them. The asynchronous operation notifies the client (in this case the windows application) of the processed stored procedure via the callbacks and event handlers codes on the client.

    pic3.jpg

  4. After the compare operation finishes, the status bar will display the summary and the duration.

    pic5small.jpg

  5. Clicking any stored procedure displayed in the list as "changed" will start the "modified" diff engine from Generic, Reusable Diff Algorithm in C# - II By Michael Potter. As you can see in this screenshot, the diff display shows what part from the left (or source) was changed/removed in the right (or target). The diff display is actually just a user control that has two richtextbox which are in sync; scrolling up/down or left/right any of the two richtextbox will make the other one to scroll too.

    tooltipsmall.jpg

  6. I think you all noticed those "weird" comments in my stored procedures like $param, $summary, $ret, and $result. Those are just special comments that this application recognize. They are used to display some info about a stored procedure such as, what are the parameters, short summary, return values, and resutlsets. Above is a tooltip which displays those info when a stored procedure in the listview is being hovered. It's not complete yet, and just a nice to have feature for this application.

Using the Code

The sample code used in this application is intuitive. There are 3 projects included in the source code. The SqlDBComparer project acts as the UI client which has event handlers that captures and interprets the event notifications sent by the class from SQLSprocCompareLibrary. The SQLSprocCompareLibrary is responsible for all the processing like connecting to database servers, retrieving list of databases and procedures, comparing and notifications. The last project is the SyncScrollingRichtextBox which is just a usercontrol used to display the difference between two stored procedures.

SQLSprocCompareLibrary Classes

The DB Class

The DB class is the single point of entry for all database connection and retrieval. It uses the ItemArgs<T> class as container for data whenever the DB class triggers an events. Making the ItemArgs generic allows any class ( in this case the DB Class) that will use it to pass any type of class as an event argument. The ConInfo class is just a container for database connection settings.

DB.jpg

Sample code of connecting and retrieving databases from a server.

C#
private void btnSrcConnect_Click(object sender, EventArgs e)
{
    Connect con = new Connect();
    con.Text = con.Text + " to Source Server";
    con.SourceTarget = "Source";


    if (con.ShowDialog(this) == DialogResult.OK)
    {
        this.SourceConInfo = con.ConInfo;

        DB db = new DB();
        db.ConInfo = con.ConInfo;

        db.OnGetDatabasesStarted += delegate(object sender2, ItemArgs<string> e2)
        {
            UIThreadCallBack cb = delegate
            {
                this.lblStatus.Text = e2.Item;
                this.cmbSrcDatabases.Enabled = false;
            };
            this.Invoke(cb);

        };
        db.OnGetDatabase += delegate(object sender2, ItemArgs<object> e2)
        {
            UIThreadCallBack cb = delegate
            {
                this.lblStatus.Text = e2.Item.ToString();
            };
            this.Invoke(cb);
        };
        db.OnGetDatabasesError += delegate(object sender2, ItemArgs<object> e2)
        {
            UIThreadCallBack cb = delegate
            {
                this.lblStatus.Text = e2.Item;
                this.cmbSrcDatabases.Enabled = true;
            };
            this.Invoke(cb);

        };
        db.OnGetDatabasesEnded += delegate(object sender2, ItemArgs<object> e2)
        {
            UIThreadCallBack cb = delegate
            {
                this.lblStatus.Text = "Ready";
                this.cmbSrcDatabases.Enabled = true;

            };
            this.Invoke(cb);

        };
        AsyncCallback callback = delegate(IAsyncResult result)
        {
            List<object> dbs = db.EndGetDatabases(result);

            UIThreadCallBack cb = delegate
            {
                this.cmbSrcDatabases.Items.Clear();
                foreach (string d in dbs)
                {
                    this.cmbSrcDatabases.Items.Add(d);
                }
                if (cmbSrcDatabases.Items.Count > 0)
                {
                    cmbSrcDatabases.Text = 
                        ConfigurationManager.AppSettings["SourceDatabase"];
                }
            };
            this.Invoke(cb);

        };
        db.BeginGetDatabases(callback, null);
    }
}

As you guys can see from the code above, after a Connect form is filled out, a DB object is created and its ConInfo property set from the ConInfo of the Connect Form. After that, event handlers (I have used anonymous methods) are being attached to then events of the DB object.  Following the event handlers is the creation of a AsyncCallback variable. That callback will be called by the DB class after the completion of the BeginGetDatabases method. It is worth pointing out that when that BeginGetDatabases method is called, it will not block the UI because it is executing asynchronously.

The SP Class

The SP class is the container for a stored procedure's information. This class has internal Database Property which references a DB object. The DB property is used by this class to retrieve its code (I mean here the stored procedure's code). I implemented a lazy loading scheme here. The Code property will only have a value upon the first call to it. The OldSproc property will contain a reference to a SP object if the Type property is ProcedureType.Changed.

SP.jpg

The lazy loading of the stored procedure's code

C#
public string Code
{
    //lazy loading :)
    get
    {
        if (this.code == null && this.Database != null)
        {
            this.Database.GetProcedureCode(this);

            // process the sp info
            if (code != null)
            {
                string[] astr = code.Split(new string[] { "\r\n" },
                    StringSplitOptions.RemoveEmptyEntries);

                //get teh summary
                Summary = "";
                string keyword = "--$summary";
                foreach (string s in astr)
                {
                    if (s.Contains(keyword))
                    {
                        Summary = (s.Replace(keyword, " ").Trim() + "\r\n");
                    }
                }
                //get all the params
                Parameters = new List<string>();
                keyword = "--$param";
                foreach (string s in astr)
                {
                    if (s.Contains(keyword))
                    {
                        Parameters.Add(s.Replace(keyword, " ").Trim() + "\r\n");
                    }
                }
                //get all the return value(s)
                Returns = new List<string>();
                keyword = "--$ret";
                foreach (string s in astr)
                {
                    if (s.Contains(keyword))
                    {
                        Returns.Add(s.Replace(keyword, " ").Trim() + "\r\n");
                    }
                }

                //get all the resultset(s)
                ResultSets = new List<string>();
                keyword = "--$result";
                foreach (string s in astr)
                {
                    if (s.Contains(keyword))
                    {
                        ResultSets.Add(s.Replace(keyword, " ").Trim().Remove(0, 2) + 
                            "\r\n");
                    }
                }
            }
        }
        return this.code;
    }
}

The Sprocs Class

There is nothing fancy to this class. This is just a container for a list of SP. It has also one method, the FindProcedureByName which is used to get SP from its contaned collection based on procedure name.

SPs.jpg

The ProcedureComparer Class

This class sole responsible is just to determine which stored procedure(s) are new, changed, or removed given two collection of SP.

PC.jpg

The ProcedureComparerManager Class

This class is what the ClientUI calls when comparing all the stored procedures between two database. It exposes 3 methods; BeginCompareProcedures and EndCompareProcedures to support asynchronous operation and Compare method that can be called in sync/asynchronous way. The SourceDB and TargeDB are DB objects that points and will connect to the two database to compare. This class has a lot of events that the client UI can use to be notified of the activity of this class, like when this class has found a new/remove/changed stored procedures, the progress, and any exception caught while executing. It should be noted that this class doesn't throws any exception because it executes in a different thread from the client UI, instead it just triggers the OnCompare event and passes a ItemArgs<object> containing the exception object. The ProgressArgs<T> is used by this class to pass information about the progress of the compare to the Client UI.

PCM.jpg

Here is the code of the Compare method. The simple logic involved in this method is first find all stored procedures that new and changed from the target SPs, and then check all SPs from source not existing (removed) in the target SPs. Any SP found was immediately sent to a event handler attached as argument in the CompareProgress method event. In the end of the method. a CompareEnded method is called that triggers an event that the Compare method is finished and the arguments of the event are the result description of the compare.

C#
        public void Compare()
{
    try
    {
        
        int newSPs = 0, removedSPs = 0, changedSPs = 0, nochangeSPs=0;

        DateTime start = DateTime.Now;
        this.CompareStarted(this, new ItemArgs<string>("Compare Started"));

        Sprocs sourceSP = this.SourceDB.GetProcedures();
        Sprocs targetSP = this.TargetDB.GetProcedures();
        int maxvalue = sourceSP.List.Count + targetSP.List.Count;
        int index = 0;

        ProcedureComparer pc = new ProcedureComparer();

        ProgressArgs<object> parg = new ProgressArgs<object>() { item = "",
            MaxValue = maxvalue, MinValue = 0, Value = index };
        this.CompareProgress(this, new CompareArgs<ProgressArgs<object>>(parg));


        foreach (SP y in targetSP.List)
        {                    
            //notify
            ProgressArgs<object> arg = new ProgressArgs<object>() { item = 
                this.SourceDB.ConInfo.DatabaseName + "." + y.Name,
                MaxValue = maxvalue, MinValue = 0, Value = ++index };
            this.CompareProgress(this, new CompareArgs<ProgressArgs<object>>(arg));

            //check if y not in sourceSP, new
            SP test = sourceSP.FindProcedureByName(y.Name);

            if (test == null)
            {
                y.Type = SP.ProcedureType.New;
                this.NewProcedure(this, new ItemArgs<SP>(y));
                ++newSPs;
            }
            else
            {

                if (pc.ProcedureChanged(test, y))
                {
                    y.Type = SP.ProcedureType.Changed;
                    y.OldSproc = test;
                    this.ChangedProcedure(this, new ItemArgs<SP>(y));
                    ++changedSPs;
                }
                else
                {
                    ++nochangeSPs;
                }
            }

        }
        //check all the sourcesp not in target to determine removed sps
        foreach (SP x in sourceSP.List)
        {

            //notify
            ProgressArgs<object> arg = new ProgressArgs<object>() { item = 
                this.SourceDB.ConInfo.DatabaseName + "." + x.Name, MaxValue = maxvalue,
                MinValue = 0, Value = ++index };
            this.CompareProgress(this, new CompareArgs<ProgressArgs<object>>(arg));

            if (targetSP.FindProcedureByName(x.Name) == null)
            {
                x.Type = SP.ProcedureType.Removed;
                this.RemovedProcedure(this, new ItemArgs<SP>(x));
                ++removedSPs;
            }
        }
        TimeSpan duration = DateTime.Now - start;

        this.CompareEnded(this, new ItemArgs<string>("Result: " + newSPs.ToString() +
            " new, " + changedSPs.ToString() + " chg, " + removedSPs.ToString() + 
            " rmv, " + nochangeSPs.ToString() + " nochg, " + "done in " + 
            duration.Duration().ToString() ));
    }
    catch (Exception ex)
    {
        try
        {
            //broadcast that an error occured
            this.CompareError(this, new ItemArgs<object>(ex));

        }
        catch { }
    }
}

I'm including also here the methods necessary to enable asynchronous operation

C#
#region Asycn Pattern

public delegate void DelegateCompareProcedures();

public IAsyncResult BeginCompareProcedures(
    AsyncCallback callback,
    Object state)
{
    return new DelegateCompareProcedures(Compare).BeginInvoke(callback, state);
}
public void EndCompareProcedures(
    IAsyncResult asyncResult)
{
    AsyncResult r = (AsyncResult)asyncResult;
    DelegateCompareProcedures del = (DelegateCompareProcedures)r.AsyncDelegate;
    del.EndInvoke(asyncResult);
}

#endregion

SyncScrollingRichtextBox Usercontrol

The SyncScrollingRichtextBox usercontrol is used in displaying the code difference between two stored procedures. It is composed of 2 richtextboxes that are in always in sync when scrolled. The codes that synchronizes them are using unmanaged code.

SCRTB.jpg

SqlDBComparer

This is the Client UI of the application. There are 2 forms; Connect and frmMain forms. The Connect Form is used to connect to any SQL Server database. The frmMain is the Main UI which contains a sortable listview that displays the stored procedures and their type,  and below it is the SyncScrollingRichtextBox usercontrol. It also has a status bar which displays information of the progress of the compare

design.jpg

Code when comparing stored procedures between 2 database

C#
public void AsyncCompare()
{
    ProcedureComparerManager pcm = new ProcedureComparerManager();
    
    this.SourceConInfo.DatabaseName = this.cmbSrcDatabases.Text;
    this.TargetConInfo.DatabaseName = this.cmbTrgDatabases.Text;

    pcm.SourceDB = new DB() { ConInfo = this.SourceConInfo };
    pcm.TargetDB = new DB() { ConInfo = this.TargetConInfo };

    //prepare the columnheaders
    {
        this.lvwSprocs.Items.Clear();
        this.lvwSprocs.Columns.Clear();

        ColumnHeader ch1 = new ColumnHeader();
        ColumnHeader ch2 = new ColumnHeader();
        ColumnHeader ch3 = new ColumnHeader();

        //30$,20%,50%
        int width = this.lvwSprocs.Width;
        ch1.Width = Convert.ToInt32((width * 0.05));
        ch2.Width = Convert.ToInt32((width * 0.33));
        ch3.Width = Convert.ToInt32((width * 0.61));

        ch1.TextAlign = HorizontalAlignment.Center;
        ch1.Text = "Status";                
        ch2.Text = "Stored Procedure";
        ch3.Text = "Summary";

        ColumnHeader tmp = new ColumnHeader();
        this.lvwSprocs.Columns.Add(tmp);
        this.lvwSprocs.Columns.Add(ch1);
        this.lvwSprocs.Columns.Add(ch2);
        this.lvwSprocs.Columns.Add(ch3);
        this.lvwSprocs.Columns.Remove(tmp);
        //this.lvwSprocs
    }

    pcm.OnCompareStarted += delegate(object sender, ItemArgs<string> e)
    {
        UIThreadCallBack cb1 = delegate
        {
            this.lblStatus.Text = e.Item;

            //disable/remove the listviewitemsorter
            this.lvwSprocs.ListViewItemSorter = null;                    
        };
        this.Invoke(cb1);
    };

    pcm.OnCompareProgress += delegate(object sender, CompareArgs<ProgressArgs<object>> e)
    {
        UIThreadCallBack cb1 = delegate
        {
            pbar.Maximum = e.Item.MaxValue;
            pbar.Minimum = e.Item.MinValue;
            pbar.Value = e.Item.Value;
            lblStatus.Text = e.Item.item.ToString();
        };
        this.Invoke(cb1);
    };

    pcm.OnNewProcedure += delegate(object sender, ItemArgs<SP> e)
    {
        UIThreadCallBack cb1 = delegate
        {
            //lazy load the code
            string tmp = e.Item.Code;

            ListViewItem lvi = this.lvwSprocs.Items.Add("New", 2);                    
            lvi.UseItemStyleForSubItems = false;

            string tip = e.Item.Name;

            tip += "\r\nParameters:";
            foreach (string s in e.Item.Parameters)
                tip += "\r\n\t" + s.Trim();

            tip += "\r\nReturns:";
            foreach (string s in e.Item.Returns)
                tip += "\r\n\t" + s.Trim();

            lvi.ToolTipText = tip;

            lvi.SubItems.Add(e.Item.Name);
            lvi.SubItems.Add(e.Item.Summary);
            lvi.Tag = e.Item;

            lvwSprocs.EnsureVisible(lvwSprocs.Items.Count - 1);
        };
        this.Invoke(cb1);
    };
    pcm.OnChangedProcedure += delegate(object sender, ItemArgs<SP> e)
    {
        UIThreadCallBack cb1 = delegate
        {
            //lazy load the code
            string tmp = e.Item.Code;

            ListViewItem lvi = this.lvwSprocs.Items.Add("Chg", 0);
            lvi.UseItemStyleForSubItems = false;

            string tip = e.Item.Name;

            tip += "\r\nParameters:";
            foreach (string s in e.Item.Parameters)
                tip += "\r\n\t" + s.Trim();

            tip += "\r\nReturns:";
            foreach (string s in e.Item.Returns)
                tip += "\r\n\t" + s.Trim();


            lvi.ToolTipText = tip;

            lvi.SubItems.Add(e.Item.Name);
            lvi.SubItems.Add(e.Item.Summary);
            lvi.Tag = e.Item;

            lvwSprocs.EnsureVisible(lvwSprocs.Items.Count - 1);
        };
        this.Invoke(cb1);
    };
    pcm.OnRemovedProcedure += delegate(object sender, ItemArgs<SP> e)
    {
        UIThreadCallBack cb1 = delegate
        {
            //lazy load the code
            string tmp = e.Item.Code;

            ListViewItem lvi = this.lvwSprocs.Items.Add("Rmv",1);                    
            lvi.UseItemStyleForSubItems = false;

            string tip = e.Item.Name;

            tip += "\r\nParameters:";
            foreach (string s in e.Item.Parameters)
                tip += "\r\n\t" + s.Trim();

            tip += "\r\nReturns:";
            foreach (string s in e.Item.Returns)
                tip += "\r\n\t" + s.Trim();

            tip += "\r\nResultset(s)";
            foreach (string s in e.Item.ResultSets)
                tip += "\r\n\t" + s.Trim();


            lvi.ToolTipText = tip;

            lvi.SubItems.Add(e.Item.Name);
            lvi.SubItems.Add(e.Item.Summary);
            lvi.Tag = e.Item;

            lvwSprocs.EnsureVisible(lvwSprocs.Items.Count - 1);
        };
        this.Invoke(cb1);
    };

    pcm.OnCompareError += delegate(object sender, ItemArgs<object> e)
    {
        UIThreadCallBack cb1 = delegate
        {
            this.lblStatus.Text = e.Item.ToString();
        };
        this.Invoke(cb1);
    };

    pcm.OnCompareEnded += delegate(object sender, ItemArgs<string> e)
    {
        UIThreadCallBack cb1 = delegate
        {
            this.lblStatus.Text = e.Item;

            lvwColumnSorter = new ListViewColumnSorter();
            this.lvwSprocs.ListViewItemSorter = lvwColumnSorter;
            lvwColumnSorter.SortColumn = 1;                    
        };
        this.Invoke(cb1);
    };
    AsyncCallback callback = delegate(IAsyncResult result)
    {
        pcm.EndCompareProcedures(result);
    };

    //start the async method
    pcm.BeginCompareProcedures(callback, null);
}

Code when displaying stored procedures in the usercontrol. The stored procedures are stored in the Tag property of the ListViewItems of the lvwSprocs Listview. If the stored procedure's type is Changed, this code compares the code of the SP and its OldSproc Code property. Otherwise, it just display the Code property value in one of the richtextboxes.

C#
public void CompareSP()
{
    ListViewItem lvi = lvwSprocs.SelectedItems[0];


    SP sp = lvi.Tag as SP;

    if (sp == null)
        return;

    this.syncScrollingRichtextBox1.RichTextBox1.Clear();
    this.syncScrollingRichtextBox1.RichTextBox2.Clear();            

    if (sp.Type == SP.ProcedureType.New)
    {
        if (sp.Code == string.Empty)
        {
            MessageBox.Show("One of the stored procedure's code probably refences" +
            "a CLR function or you dont have permission to access the database objects", 
            "Invalid Code to Compare");
            return;
        }
        this.syncScrollingRichtextBox1.RichTextBox2.Text = sp.Code;                
        this.syncScrollingRichtextBox1.Focus();
    }
    else if (sp.Type == SP.ProcedureType.Removed)
    {
        if (sp.Code == string.Empty)
        {
            MessageBox.Show("One of the stored procedure's code probably refences" +
            "a CLR function or you dont have permission to access the database objects",
            "Invalid Code to Compare");
            return;
        }
        this.syncScrollingRichtextBox1.RichTextBox1.Text = sp.Code;                
        this.syncScrollingRichtextBox1.Focus();
    }

    else if (sp.Type == SP.ProcedureType.Changed)
    {
        if (sp.Code == string.Empty || sp.OldSproc.Code == string.Empty)
        {
            MessageBox.Show("One of the stored procedure's code probably refences" +
            "a CLR function or you dont have permission to access the database objects",
            "Invalid Code to Compare");
            return;
        }

        DiffList_TextData source = new DiffList_TextData(sp.OldSproc.Code);
        DiffList_TextData dest = new DiffList_TextData(sp.Code);

        double time = 0;
        DiffEngine de = new DiffEngine();
        time = de.ProcessDiff(source, dest, DiffEngineLevel.FastImperfect);

        ArrayList rep = de.DiffReport();

        StringBuilder sb1 = new StringBuilder();
        StringBuilder sb2 = new StringBuilder();

        int i = 0;
        int line = 0;
        string strLine = "";
        foreach (DiffResultSpan drs in rep)
        {
            switch (drs.Status)
            {
                case DiffResultSpanStatus.DeleteSource:
                    for (i = 0; i < drs.Length; i++)
                    {
                        strLine = String.Format("{0:0000}", ++line) + "\t";

                        string str1 = strLine + source.GetByIndex(drs.SourceIndex + 
                            (i)).ToString();
                        string str2 = strLine + "".PadRight(str1.Length, ' ') + "\r\n";

                        int len = str1.Length;
                        int start = 
                            this.syncScrollingRichtextBox1.RichTextBox1.Text.Length;

                        this.syncScrollingRichtextBox1.RichTextBox1.AppendText(str1);
                        this.syncScrollingRichtextBox1.RichTextBox1.SelectionStart = start;
                        this.syncScrollingRichtextBox1.RichTextBox1.SelectionLength = len;
                        this.syncScrollingRichtextBox1.RichTextBox1.SelectionBackColor =
                           Color.Gold;

                        len = str2.Length;
                        start = this.syncScrollingRichtextBox1.RichTextBox2.Text.Length;

                        this.syncScrollingRichtextBox1.RichTextBox2.AppendText(str2);
                        this.syncScrollingRichtextBox1.RichTextBox2.SelectionStart = start;
                        this.syncScrollingRichtextBox1.RichTextBox2.SelectionLength = len;
                        this.syncScrollingRichtextBox1.RichTextBox2.SelectionBackColor =
                            Color.Silver;
                    }
                    break;
                case DiffResultSpanStatus.NoChange:
                    for (i = 0; i < drs.Length; i++)
                    {
                        strLine = String.Format("{0:0000}", ++line) + "\t";

                        string str1 = strLine + source.GetByIndex(drs.SourceIndex + 
                           (i)).ToString();
                        string str2 = strLine + dest.GetByIndex(drs.DestIndex + 
                           (i)).ToString(); ;

                        this.syncScrollingRichtextBox1.RichTextBox1.AppendText(str1);
                        this.syncScrollingRichtextBox1.RichTextBox2.AppendText(str2);

                    }
                    break;
                case DiffResultSpanStatus.AddDestination:
                    for (i = 0; i < drs.Length; i++)
                    {
                        strLine = String.Format("{0:0000}", ++line) + "\t";

                        string str2 = strLine + dest.GetByIndex(drs.DestIndex +
                           (i)).ToString();
                        string str1 = strLine + "".PadRight(str2.Length, ' ') + "\r\n";

                        int len = str1.Length;
                        int start = 
                           this.syncScrollingRichtextBox1.RichTextBox1.Text.Length;
                        this.syncScrollingRichtextBox1.RichTextBox1.AppendText(str1);
                        this.syncScrollingRichtextBox1.RichTextBox1.SelectionStart = start;
                        this.syncScrollingRichtextBox1.RichTextBox1.SelectionLength = len;
                        this.syncScrollingRichtextBox1.RichTextBox1.SelectionBackColor = 
                            Color.Silver;


                        len = str2.Length;
                        start = this.syncScrollingRichtextBox1.RichTextBox2.Text.Length;
                        this.syncScrollingRichtextBox1.RichTextBox2.AppendText(str2);
                        this.syncScrollingRichtextBox1.RichTextBox2.SelectionStart = start;
                        this.syncScrollingRichtextBox1.RichTextBox2.SelectionLength = len;
                        this.syncScrollingRichtextBox1.RichTextBox2.SelectionBackColor = 
                            Color.Gold;

                    }
                    break;
                case DiffResultSpanStatus.Replace:
                    for (i = 0; i < drs.Length; i++)
                    {
                        strLine = String.Format("{0:0000}", ++line) + "\t";

                        string str1 = strLine + source.GetByIndex(drs.SourceIndex + 
                            (i)).ToString();
                        string str2 = strLine + dest.GetByIndex(drs.DestIndex + 
                            (i)).ToString();

                        int len = str1.Length;
                        int start =
                            this.syncScrollingRichtextBox1.RichTextBox1.Text.Length;

                        this.syncScrollingRichtextBox1.RichTextBox1.AppendText(str1);
                        this.syncScrollingRichtextBox1.RichTextBox1.SelectionStart = start;
                        this.syncScrollingRichtextBox1.RichTextBox1.SelectionLength = len;
                        this.syncScrollingRichtextBox1.RichTextBox1.SelectionBackColor = 
                            Color.Chocolate;
                        this.syncScrollingRichtextBox1.RichTextBox1.SelectionColor =
                            Color.White;

                        len = str2.Length;
                        start = this.syncScrollingRichtextBox1.RichTextBox2.Text.Length;

                        this.syncScrollingRichtextBox1.RichTextBox2.AppendText(str2);
                        this.syncScrollingRichtextBox1.RichTextBox2.SelectionStart = start;
                        this.syncScrollingRichtextBox1.RichTextBox2.SelectionLength = len;
                        this.syncScrollingRichtextBox1.RichTextBox2.SelectionBackColor = 
                            Color.Chocolate;
                        this.syncScrollingRichtextBox1.RichTextBox2.SelectionColor = 
                            Color.White;
                    }
                    break;
            }
        }

        this.syncScrollingRichtextBox1.RichTextBox1.SelectionStart = 0;
        this.syncScrollingRichtextBox1.RichTextBox2.SelectionStart = 0;
        this.syncScrollingRichtextBox1.RichTextBox1.Focus();
    }
    splitContainer1.Panel2Collapsed = false;

}

Points of Interest

One of the improvements I am planning to include in this application is a comparison of database table structures and records. As of now, I am learning programming constructs that I think I need before I start those things. Hope that I can finished these functionalities and share them with you guys.

Known Issue(s)

There is one subtle issue with the application when comparing. During my coding of the tsql query to be used for retrieving the stored procedures, I did not included a filter to return only the stored procedures; some of the functions are also returned in the resultset. When I found out that, I thought it was just okay because a function is just like a stored procedure, and great if it can also compare user-defined functions. What then I missed was that some functions may happen to references assemblies/dlls, aka SQL Server CLR function. What I did is that when the application encounters a CLR function during comparing of stored procedures, it will simply display this message.

clrfunction.jpg

License

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


Written By
Database Developer Virtify Inc.
Philippines Philippines
I am a database developer in my current employment for nearly 4 years. I'm proficient in programming using C/C++, C#, and T-sql. When I am not working, I like to read books(non-fiction specially), play mmorpgs where I can be what I don't want to be Smile | :) , and going outdoors.

Comments and Discussions

 
QuestionLove u Bro Superb MindBlowing Work Pin
jayvengs97-Jun-22 21:16
jayvengs97-Jun-22 21:16 
QuestionExcellent! Pin
Member 139962124-Mar-20 1:07
Member 139962124-Mar-20 1:07 
QuestionIs it possible to compare two different SP that are .sql files but not yet loaded on the db? Pin
Member 1301289820-Feb-17 7:54
Member 1301289820-Feb-17 7:54 
QuestionThanks for Code Pin
skhacker10520-Apr-16 4:32
skhacker10520-Apr-16 4:32 
QuestionExcellent Pin
Fedwards28-Jun-15 10:06
Fedwards28-Jun-15 10:06 
QuestionGood Pin
basemshetewy6-Jul-14 23:55
basemshetewy6-Jul-14 23:55 
GeneralFantastic Pin
HMitchell15-Aug-13 4:57
HMitchell15-Aug-13 4:57 
General20 main differences between Stored procedures and Functions in Sql Server Pin
Lalit24rocks15-May-13 0:30
Lalit24rocks15-May-13 0:30 
QuestionAmazing! Pin
glienard1-Mar-13 2:48
glienard1-Mar-13 2:48 
QuestionVery nice code Pin
pt140129-Dec-12 23:57
pt140129-Dec-12 23:57 
Generalwhen I needed it most Pin
gbilljacktuna28-Nov-12 12:20
gbilljacktuna28-Nov-12 12:20 
Generalgreat ! Pin
laurent89766525-Nov-12 22:43
laurent89766525-Nov-12 22:43 
GeneralMy vote of 5 Pin
Kanasz Robert24-Sep-12 5:55
professionalKanasz Robert24-Sep-12 5:55 
GeneralMy vote of 5 Pin
StianSandberg10-Aug-12 2:34
StianSandberg10-Aug-12 2:34 
QuestionThe RichTextBox backgroud is set to Black Pin
Member 911885013-Jun-12 22:24
Member 911885013-Jun-12 22:24 
AnswerRe: The RichTextBox backgroud is set to Black Pin
Christopher Ayroso9-Jul-12 15:57
Christopher Ayroso9-Jul-12 15:57 
GeneralRe: The RichTextBox backgroud is set to Black Pin
k_e_newton17-Jul-12 0:55
k_e_newton17-Jul-12 0:55 
AnswerRe: The RichTextBox backgroud is set to Black Pin
pt140129-Dec-12 22:17
pt140129-Dec-12 22:17 
GeneralExcellent Code Pin
T.Saravanann1-Mar-12 1:27
T.Saravanann1-Mar-12 1:27 
GeneralRe: Excellent Code Pin
Christopher Ayroso19-Mar-12 16:58
Christopher Ayroso19-Mar-12 16:58 
QuestionPlanning an updated version Pin
Christopher Ayroso8-Jan-12 20:02
Christopher Ayroso8-Jan-12 20:02 
AnswerRe: Planning an updated version Pin
pt140129-Dec-12 13:40
pt140129-Dec-12 13:40 
GeneralYou impressed me Pin
Thabet Tech6-Jan-12 23:18
Thabet Tech6-Jan-12 23:18 
GeneralRe: You impressed me Pin
Christopher Ayroso8-Jan-12 19:35
Christopher Ayroso8-Jan-12 19:35 
GeneralMy vote of 5 Pin
spwan5id5-Jan-12 9:09
spwan5id5-Jan-12 9:09 

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.