Search for Stored Procedures in source code
During the development process of an ASP.NET web application, the number of stored procedures used in a SQL Server 2000 database can grow alarmingly. More often than not, I forget to document which stored procedures are being used and which are redundant. So, I decided to build a little utility that searched through my source code to find all instances of stored procedures being used. My goal was to have a web page that listed the procedures being used on each page. In IE6, I could then right-click on the table and export the list to Excel. Useful for future documentation.
First of all we start by looping through directories in our root folder:
private string FileTypes="*.cs";
private void DirSearch(string Dir)
{
try
{
foreach (string f in Directory.GetFiles(Dir, FileTypes))
{
ReadFile(f);
}
foreach (string d in Directory.GetDirectories(Dir))
{
DirSearch(d);
}
}
catch (Exception e)
{
Response.Write(e.Message);
}
}
Like many other, all my stored procedures begin with "sp", so we can read each file and look for all instances of "sp*" and store them in a datatable.
private string SearchExpression="\"(sp[^\"]+)\"";
private void ReadFile(string fn)
{
StreamReader s = new StreamReader(fn);
string read = s.ReadToEnd();
s.Close();
Regex r = new Regex(SearchExpression);
MatchCollection mc=r.Matches(read);
foreach (Match m in mc)
{
DataRow dr=dt.NewRow();
dr["SP"]=m.Groups[1];
dr["Page"]=fn.Substring(Folder.Length+1);
dt.Rows.Add(dr);
}
}
Next, we can bind the Datatable to a Datagrid:
private string Folder=@"c:\mywebsite";
private void BindData()
{
dt=new DataTable();
dt.Columns.Add("SP");
dt.Columns.Add("Page");
DirSearch(Folder);
DataView dv=dt.DefaultView;
dv.Sort=SortColumn;
DataGrid1.DataSource=dv;
DataGrid1.DataBind();
}
And then bring it all together when the pages loads:
private void Page_Load(Object src,EventArgs e)
{
if (!IsPostBack)
{
SortColumn="SP";
BindData();
}
}
You can download a demonstration of the code by clicking the link below.
Download file
