SQL-DMO: Search and Regenerate Stored Procedures in Seconds

• Download source - 287.8 KB
• Download another version developed using SMO (SQL Server Management Object) instead - 651.83 KB

Introduction

Creating, generating and altering stored procedures are common tasks when we are working with database applications. These tasks won't take up our time if our database has a small number of stored procedures.

But for databases with thousands of stored procedures, it takes up our time to identify the stored procedure name in the list and then do some clicks to generate its script. To prevent these boring tasks, I spent a couple of hours to create a very simple tool for quick searching and regenerating stored procedure scripts using SQL-DMO Object Library.

Using the Code

The code is very simple and this sample is not a perfect one, but it helps my work. ;)

using System;
using System.Collections;
using SQLDMO;
using System.Text;

namespace SQLScriptGen
{
    /// <summary>
    /// The Utility class present for a SQL Server instant
    /// </summary>
    public class SQLServerUtil
    {
        private SQLServer myServer;
        private bool isConnected = false;
        public SQLServerUtil()
        {
            myServer = new SQLServer();
        }

        public delegate void delegateSendText (string info);
        public event delegateSendText OnInfo;
        public event delegateSendText OnSearchFound;

        /// <summary>
        /// Get List of available SQL Server on network
        /// </summary>
        /// <returns />
        public  ArrayList GetServers()
        {
            ArrayList ret = new ArrayList();
            try
            {
                NameList nl = myServer.Application.ListAvailableSQLServers();
                int i, n = nl.Count;
                for (i = 0; i< n; i++)
                {
                    string sn = nl.Item(i);
                    if (sn != null)
                        ret.Add(sn);
                }
            }
            catch (System.Exception ex)
            {
                if (OnInfo != null)
                    OnInfo(ex.Message);
            }

            return ret;
        }

        /// <summary>
        /// Connect to a SQL Server using Windows Authentication
        /// </summary>
        public bool Connect(string serverName)
        {
            Disconnect();
            try
            {
                myServer.LoginTimeout = 15;
                myServer.LoginSecure = true;
                myServer.Connect(serverName,null , null);
            }
            catch (Exception ex)
            {
                isConnected = false;
                if (OnInfo != null)
                    OnInfo(ex.Message);
            }
            isConnected = true;
            return isConnected;
        }

        /// <summary>
        /// Disconnect from the server 
        /// </summary>
        private void Disconnect()
        {
            try
            {
                myServer.DisConnect();
            }
            catch (Exception ex)
            {
                if (OnInfo != null)
                    OnInfo(ex.Message);
            }
        }

        /// <summary>
        /// Connect to a SQL Server using SQL Authentication
        /// </summary>
        public bool Connect(string serverName, string userName, string password)
        {
            Disconnect();
            try
            {
                myServer.LoginSecure = false;
                myServer.Connect(serverName, userName, password);
            }
            catch (Exception ex)
            {
                isConnected = false;
                if (OnInfo != null)
                    OnInfo(ex.Message);
            }
            isConnected = true;
            return isConnected;
        }

        /// <summary>
        /// Retrieve list of available Database names
        /// </summary>
        public ArrayList GetDatabaseNames()
        {
            ArrayList ret = new ArrayList();
            try
            {
                foreach (_Database db in myServer.Databases)
                {
                    if (db != null)
                        ret.Add(db.Name);
                }

            }
            catch (System.Exception ex)
            {
                if (OnInfo != null)
                    OnInfo(ex.Message);
            }
            return ret;
        }

        /// <summary>
        /// Get script of a store procedure
        /// </summary>
        public string GetSPScript(string dbName, string spName, bool createFile)
        {
            string ret = "";
            try
            {
                string fileName = "output\\" + spName + ".sql";
                if (!createFile)
                    fileName = null;
                ret = myServer.Databases.Item
		  (dbName, "dbo").StoredProcedures.Item(spName, "dbo")
                    .Script(SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Drops 
                    | SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_IncludeHeaders
                    | SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Default
                    , fileName,
                    SQLDMO.SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_Default );
            }
            catch (Exception ex)
            {
                if (OnInfo != null)
                    OnInfo(ex.Message);

            }
            return ret;
        }

        /// <summary>
        /// Simple searching method
        /// </summary>
        public void SearchSP(string dbName, string searchText, bool inNameOnly)
        {
            try
            {
                StoredProcedures colSP = myServer.Databases.Item
					(dbName, "dbo").StoredProcedures;
            
                if (inNameOnly)
                {
                    foreach (_StoredProcedure sp in colSP)
                    {

                        if (sp.Name.IndexOf(searchText) >=0)
                        {
                            if (OnSearchFound != null)
                            {
                                OnSearchFound(sp.Name);
                                System.Windows.Forms.Application.DoEvents();
                            }
                        }
                    }
                }
                else
                {
                    foreach (_StoredProcedure sp in colSP)
                    {

                        if (sp.Text.IndexOf(searchText) >=0)
                        {
                            if (OnSearchFound != null)
                            {
                                OnSearchFound(sp.Name);
                            }
                        }
                    }
                }
            }
            catch (System.Exception ex)
            {
                if(OnInfo != null)
                {
                    OnInfo(ex.Message);
                }
            }
        }
    }
} 

Have fun with this!