Click here to Skip to main content
15,895,370 members
Articles / Database Development / SQL Server

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 126.2K   6.5K   81  
Windows application that compares stored procedures between two SQL Server database
using System;
using System.Collections.Generic;
using System.Text;

using SQLSprocCompareLibrary.Database;

namespace SQLSprocCompareLibrary
{
    public class SP
    {
        internal string code;

        public enum ProcedureType
        {
            Unchanged = 0,
            Removed = 1,
            New = 2,
            Changed = 3
        }

        public string Schema { get; set; }
        public string Name { get; set; }
        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;
            }
        }
        public List<string> Parameters { get; set; }
        public List<string> Returns { get; set; }
        public List<string> ResultSets { get; set; }
        public string Summary { get; set; }
        public SP OldSproc { get; set; }
        public ProcedureType Type { get; set; }

        public SP()
        {
            this.Schema = string.Empty;
            this.Name = string.Empty;
            this.code = null;
            this.Parameters = new List<string>();
            this.Returns = new List<string>();
            this.ResultSets = new List<string>();

            this.Type = ProcedureType.Unchanged;
        }

        public override string ToString()
        {
            return this.Name;
        }

        internal DB Database { get; set; }
    }
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

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