Find Stored Procedures called within a procedure

• Download Complete Sourcecode - 21.6 Kb

Last Updated on 26-04-'05.

Introduction

It's tedious to find all calls made to all SP from a stored procedure and generating reports in case we have large databases, this tool is aimed to script all Sp's and extracts SP calls, it also strips out additional information such as author name and description.

Image shows Application in progress...

We all use stored procedures extensively to place business logic in development. Most of the time, the calling of a Stored Procedure is well defined in proper documents / other details.

Say, what if we need to find out as to how many calls a stored procedure makes to another procedure or which of the stored procedures are called by UI / other layer and not by the SQL SPs.

I tried to answer this problem by creating an application which will fetch the SQL metadata (for a given database) and will script entire SP list..

We can aim to extract information in four different phases:

• Extract information from DataSource (enter info in UI-textbox).
• Create formatted DataSet from the Source.
• Extract relevant information and update DataSet.
• Present the DataSet via XSLT.

In SQL Server, we can extract relevant information from DB via INFORMATION_SCHEMA objects.

select Routine_Name , Routine_Definition, '' as 'DESCRIPTION', 
       '' as 'AUTHOR', '' as 'AUTHOR_Class', '' as 'DESCRIPTION_Class' 
       from information_schema.routines where Routine_Type='procedure'

We can format the DataSet by creating relations and adding rows to it.

dtCol = New DataColumn("ID", System.Type.GetType("System.Int32"))
dtCol.AutoIncrement = True
dtCol.AutoIncrementSeed = 1
dtCol.AutoIncrementStep = 1
dtChild.Columns.Add(dtCol)
dtCol = New DataColumn("Calling_SP", System.Type.GetType("System.String"))
dtChild.Columns.Add(dtCol)
dtCol = New DataColumn("Called_SP", System.Type.GetType("System.String"))
dtChild.Columns.Add(dtCol)
ds.Tables.Add(dtChild)
ds.EnforceConstraints = False
Dim drRel As DataRelation
drRel = ds.Relations.Add("SP-LINK", ds.Tables(0).Columns("Routine_Name"), 
ds.Tables(1).Columns("Calling_SP"), False)
drRel.Nested = True

Extracting relevant information is parameterized, and can be placed in application configuration file for customization. This is a sample code to extract stored procedure description:

Function GetDesc(ByVal strInput As String) As String
        Dim objMatch As Match
        Dim objRegEx = New Regex( _
            strDESCRIPTIONREGEX, _
            RegexOptions.IgnoreCase _
            Or RegexOptions.Multiline _
            Or RegexOptions.Compiled _
            )
        Dim strRetVal As String = ""
        objMatch = objRegEx.Match(strInput)
        If objMatch.Success Then
            strRetVal = objMatch.Groups(0).Value.ToUpper()
            strRetVal = strRetVal.Replace("  ", " ").Trim()
        End If
        Return strRetVal
End Function

Similarly, we can remove comments for extracting calls by this code:

Function GetPureText(ByVal strInput As String) As String
  Dim objMatch As Match
  Dim strRetVal As String = ""
  strRetVal = Regex.Replace(strInput, "/\*[\w\W]*?\s*\*/", "", _
     RegexOptions.IgnoreCase Or RegexOptions.Multiline Or 
     RegexOptions.Compiled)
  Return strRetVal
End Function

Please note that the configurations (regular expressions) are stored in application configuration file. The below code shows the function snippet which strips the SP calls:

 dr("DESCRIPTION") = GetDesc(strSPText).Trim
  dr("AUTHOR") = GetAUTH(strSPText).Trim
  dr("AUTHOR_Class") = IIf(Convert.ToString(dr("AUTHOR")) = "", _
           "Blank", "MyClass")
  dr("DESCRIPTION_Class") = IIf(Convert.ToString(dr("DESCRIPTION")) = "", _
           "Blank", "MyClass")
  strSPText = GetPureText(strSPText)
  '(exec[\w\W]*?)(m[\w\W]*?)(@|\s+)

  '(exec[\w\W]*?)(m|s[\w\W]*?)(@|\s+)

  strSPREGEX = strSPREGEX.Replace("@@@LETTER@@@", txtLetter.Text)
  objMatchCol = GetMatchedCollection(strSPText, strSPREGEX)
  For Each matchObj In objMatchCol

Now we have the formatted DataSet. Apply pre-defined XSLT and extract the report (Please see attached ZIP files for the same).

Code snippet:

Dim sb As StringBuilder = New StringBuilder()
Dim sw As StringWriter = New StringWriter(sb)
Dim dataDoc As XmlDataDocument = New XmlDataDocument(ds)
Dim transform As XslTransform = New XslTransform()
transform.Load(AppDomain.CurrentDomain.BaseDirectory & "transform.xslt")
transform.Transform(dataDoc, Nothing, sw)
If File.Exists(AppDomain.CurrentDomain.BaseDirectory & "MainReport.html") Then
  File.Delete(AppDomain.CurrentDomain.BaseDirectory & "MainReport.html")
End If
Dim swMain As StreamWriter
swMain = File.CreateText(AppDomain.CurrentDomain.BaseDirectory & "MainReport.html")

Please check the config file entry required for the application.

  <!-- (exec[\w\W]*?)(@@@LETTER@@@[\w\W]*?)(@|\s+) // for reference.  
  Here @@@LETTER@@@ will be the input Letter from Application 
  (this can also be ommited)  -->  
  <add key="SP_REGEX" value="(exec[\w\W]*?)(@@@LETTER@@@[\w\W]*?)(@|\s+)" />  
  <!--
  Author name Extraction, you can write ur own custom Author Extraction logic.
  -->
  <add key="AUTHOR_REGEX" value="(AUTHOR[\w\W]*?)(\n)" />
  <!--
  Description Extraction, you can write ur own custom Description Extraction logic.
  -->
  <add key="DESCRIPTION_REGEX" value="(DESCRIPTION[\w\W]*?)(\s*CHANGE HISTORY)" />

The application uses XML - XSLT to format the report, this helps in finding called stored procedures relatively and anchoring them in a nested call.

Below is an XSLT file extract:

<td valign='top' width='5%'>
     <font size="4">
      <b>
       <font size="1">SP Name: </font>
       <a>
        <xsl:attribute name="id">
         <xsl:value-of select="Routine_Name" />
        </xsl:attribute>
        <xsl:attribute name="Title">
         <xsl:value-of select="DESCRIPTION" />
        </xsl:attribute>
        <xsl:attribute name="name">
         <xsl:value-of select="Routine_Name" />
        </xsl:attribute>
        <xsl:attribute name="href">#<xsl:value-of select="Routine_Name" />
        </xsl:attribute>
        <xsl:value-of select="Routine_Name" />
       </a>
      </b>
     </font>
    </td>

The code here is of Visual Studio 2002.

You can get the formatted, relative-anchor based report for all stored proc in the selected database. Check out Brip Blog for more .NET / SQL Server kicks...... Join Our Group.

Recent Updates

• Fixed size bug and now data is extracted from System tables instead of Information_schema.
• Fixed XSLT bug.