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" >Results<asp:Label>
</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 well as you expected but it 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.