AKH Online, Inc

Accomplished Internauts

Other Sites in the News

November 13, 2024
Types of Technology: A Complete Overview [2025] - Simplilearn

October 23, 2024
iPhone - Britannica

View Stored Procedure 'Source Code' using CFML

Let's say that you've been brought on-site to help a client with some code or database emergency. Let's also say that you don't have time or access to a traditional SQL IDE (like SQL Server Management Studio, Toad or Eclipse SQL Explorer), but you need to see what's going on 'under the hood' during a procedure call...

How then, could you peek at the source code ('definition') used to compile binary objects like Stored Procedures, Views, Triggers or Functions?

Just place the following file in the application's directory and call it from your browser. I named mine "sqlSource.cfm":

<cfparam name="mode" default="getDS" />


<cfif mode eq "objSource">
   
   <cfparam name="form.ObjID" default="0" />
   <cfquery name="getSource" datasource="#form.dsName#">
   select definition
   from sys.sql_modules
   where object_id = #val(form.ObjID)#
</cfquery>
<cfif getSource.recordCount>
<cfelse>
   <strong>No object found</strong>
   <cfset mode = "objList" />
</cfif>
</cfif>

<cfif mode eq "objList">

<cfparam name="form.objType" default="V" />
<cfquery name="getObjects" datasource="#form.dsName#">
   select name, object_id
   from sys.objects (nolock)
   where type = '#form.objType#' and is_ms_shipped = 0
   order by name
</cfquery>
<cfif getObjects.recordCount>
<cfelse>
   <strong>No records found</strong>
   <cfset mode = "getDS" />
</cfif>
</cfif>

<cfif mode eq "getDS">

   <cfset dataSourceObj=createobject("java","coldfusion.server.ServiceFactory").getDatasourceService().getDatasources() />
<cfset msSqlList = "" />

<cfloop collection="#dataSourceObj#" item="unit">
   <cfif ucase(dataSourceObj[unit].DRIVER) eq "MSSQLSERVER">
       <cfset msSqlList = listAppend(msSqlList,unit) />
   </cfif>
   
</cfloop>
</cfif>



<html>
<body>
<cfoutput>
   <cfif mode eq "getDS">
       <form name="getDSForm" action="#cgi.SCRIPT_NAME#" method="POST">
           <p>
               Pick Datasource<br />
               <select name="dsName" size="#listLen(msSqlList)#">
                   <cfloop from="1" to="#listLen(msSqlList)#" index="i">
                       <option value="#listGetAt(msSqlList,i)#">#listGetAt(msSqlList,i)#</option>
                   </cfloop>
               </select>
           </p>

           <p>
               Show me<br />
               <input type="radio" name="objType" value="V" checked> Views<br />
               <input type="radio" name="objType" value="P"> Stored Procedures<br />
               <input type="radio" name="objType" value="TR"> Triggers<br />
               <input type="radio" name="objType" value="FN"> Functions<br />
           </p>

           <p>
               <input type="Submit" value="Next" />
               <input type="hidden" name="mode" value="objList" />
           </p>
       </form>
   </cfif>

   <cfif mode eq "objList">
       <form name="getDSForm" action="#cgi.SCRIPT_NAME#" method="POST">
           <p>
               Pick Object<br />
               <select name="ObjID" size="#min(getObjects.recordCount,15)#">
                   <cfloop query="getObjects">
                       <option value="#getObjects.object_id#">#getObjects.name#</option>
                   </cfloop>
               </select>
           </p>

           <p>
               <input type="Submit" value="Show Source" />
               <input type="hidden" name="mode" value="objSource" />
               <input type="hidden" name="dsName" value="#form.dsName#" />
           </p>

           <p>
               <input type="button" value="Back" onClick="history.go(-1);" />
           </p>
       </form>
   </cfif>

   <cfif mode eq "objSource">
       <form name="getDSForm" action="#cgi.SCRIPT_NAME#" method="POST">
           <p>
               Source<br />
               <textarea name="txtSource" cols="80" rows="30" onfocus="this.select()">#getSource.definition#</textarea>
           </p>
           <input type="button" value="Back" onClick="history.go(-1);" />
       </form>
   </cfif>
</cfoutput>
</body>
</html>

Be sure to remove the file when your done!

If you just see a blank area where the source code should be, then the datasource's account has limited access. Have the DBA grant your account the 'View Any Definition' permission, and things should show up after that.