SQL-DMO and searching from meta-data

Lately I faced the need to search for specified strings in all stored procedures in given database. I found SQL-DMO library very useful for it. So, ten minutes later I had a simple form that fits my needs perfectly. And some minutes later I had there some more cool features. As a result I had form that enables me to search for strings from stored procedures, user defined functions and views.

Also I was able to search strings from mentioned objects names. I tested it on a little bit larger MS SQL Server database, iterating approximately through 500 database objects and searching for given string and I mentioned no performance problems. So, here’s the example.

Getting Started

SQLDMO is provided with MSDE and MS SQL Server. SQLDMO stands for SQL Data Management Objects. It is library that enables you to access MS SQL Server
and MSDE meta data easily and write tools to manage database.

Form

At first let’s create a simple form. I made my project (long ago) on Visual Studio.Net.


<%@Page language="c#" Codebehind="Default.aspx.cs" AutoEventWireup="false" 
Inherits="DbTools.Default" %>
<html
>
<
head>
    <title>Search for string in SP’s</title
>
</
head>

<body>
    <form id="Form1" method="post" runat="server">
        <h3>Search for string in all stored procedures</h3>
        <p>
            Text to search: <asp:TextBox id="txtSTR" runat="server" />
            <asp:Button id="btnGO" runat="server" Text="Search" />
        </p>
        <p>
            <asp:Label id="lblResults" runat="server" Font-Bold="True" Text="Results" />
        </p>
        <p>
            <asp:Label id="lblNoResults" runat="server"/>
            <asp:DataGrid id="dbgSPS" runat="server" />
        </p>
    </form
>
</
body
>
</
html
>

Code is simple and needs no additional comments here. Make sure you make reference to SQL-DMO library. Also don’t forget to import SQ-LDMO in code-behind file. You will find all necessary comments in code below. Just add this code to code-behind file of the page you previously created.


private void Page_Load(object sender, System.EventArgs e)
{
   
// Let's initialize controls
    this.lblNoResults.Visible = false
;
   
this.lblResults.Visible = false
;
   
this.dbgSPS.Visible = false
;

   
// If not postback clear search box
    if (!this
.IsPostBack)
    {
       
this.txtSTR.Text = ""
;
       
return
;
    }
}

private void btnGO_Click(object sender, System.EventArgs
e)
{
   
// No search string was given
    if (this.txtSTR.Text.Trim() == ""
)
    {
       
this.lblNoResults.Visible = true
;
       
this.lblResults.Visible = true
;
       
this.lblNoResults.Text = "Sisesta otsitav täheühend!"
;
       
return
;
    }

   
DataTable
dt;
   
DataRow
dr;
   
SQLServer2Class
srv;
   
_Database2
db;
   
StoredProcedures
sps;
   
UserDefinedFunctions
fns;
   
Views
vws;


   
// Initialize table for results
    dt = new DataTable
();
    dt.Columns.Add(
new DataColumn("Type"
));
    dt.Columns.Add(
new DataColumn("Name"
));

   
// Create connection and ask list of stored procedures
    srv = new SQLServer2Class
();
    srv.Connect(
"(local)", "myUserName", "myPassword"
);
    db = (
_Database2)srv.Databases.Item("myDatabase", null
);
    sps = db.StoredProcedures;

   
// Check the body of procedures and add procedures with match
    // to results table.
    foreach (StoredProcedure sp in
sps)
       
if (sp.Text.ToLower().IndexOf(this
.txtSTR.Text.ToLower()) > -1 ||
                sp.Name.ToLower().IndexOf(
this
.txtSTR.Text.ToLower()) > -1)
        {
            dr = dt.NewRow();
            dr[
"Type"] = "Stored procedure"
;
            dr[
"Name"
] = sp.Name;
            dt.Rows.Add(dr);
        }

   
// Check the body of functions and add functions with match
    // to results table
    fns = db.UserDefinedFunctions;
   
foreach (UserDefinedFunction fn in
fns)
       
if (fn.Text.ToLower().IndexOf(this
.txtSTR.Text.ToLower()) > -1 ||
            fn.Name.ToLower().IndexOf(
this
.txtSTR.Text.ToLower()) > -1)
        {
            dr = dt.NewRow();
            dr[
"Type"] = "Function"
;
            dr[
"Name"
] = fn.Name;
            dt.Rows.Add(dr);
        }
    fns =
null
;

   
// Check the body of views and add views with match
    // to results table
    vws = db.Views;
   
foreach (View vw in
vws)
       
if (vw.Text.ToLower().IndexOf(this
.txtSTR.Text.ToLOwer()) > -1 ||
            vw.Name.ToLower().IndexOf(
this
.txtSTR.Text.ToLower()) > -1)
        {
            dr = dt.NewRow();
            dr[
"Type"] = "View"
;
            dr[
"Name"
] = vw.Name;
            dt.Rows.Add(dr);
        }
    vws =
null
;

   
// Kill objects
    sps = null
;
    db =
null
;
    srv.DisConnect();
    srv =
null
;
   
this.lblResults.Visible = true
;

   
// No results
    if
(dt.Rows.Count == 0)
    {
       
this.lblNoResults.Text = "No results"
;
       
this.lblNoResults.Visible = true
;
       
return
;
    }

   
// Show results
    this.lblNoResults.Text = "Results"
;
    dt.DefaultView.Sort =
"Name"
;
   
this
.dbgSPS.DataSource = dt;
   
this
.dbgSPS.DataBind();
   
this.dbgSPS.Visible = true;
    dt.Dispose();
}

Although solution isn’t maybe so good as you expected it still works fine. I have mentioned no performance issues this far using this code. But if you know better solution to this problem, please drop me a note here.


2 thoughts on “SQL-DMO and searching from meta-data

Leave a Reply

Your email address will not be published. Required fields are marked *