AKH Online, Inc
Accomplished Internauts
Other Sites in the News
- October 21, 2024
- Gartner Top 10 Strategic Technology Trends for 2025 - Gartner
- 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":
<!--- This page will let you select a ColdFusion datasource, then one of the compiled objects --->
<!--- from the database within (View, Procedure, Trigger). It will then show the source code. --->
<!--- 20130729 AKHONLINE.COM (currently only for MS SQL Server, but one could probably add others) --->
<!--- starting mode is the default --->
<cfparam name="mode" default="getDS" />
<!--- modes are executed in reverse order, so if one fails we drop down to the last one --->
<cfif mode eq "objSource">
<!--- fetch the source code used to compile the object from MS SQL SERVER --->
<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">
<!--- fetch a list of objects in this datasource from MS SQL SERVER --->
<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">
<!--- fetch a list of datasources on this CFML server --->
<cfset dataSourceObj=createobject("java","coldfusion.server.ServiceFactory").getDatasourceService().getDatasources() />
<cfset msSqlList = "" />
<!--- add others, like oracleSqlList --->
<cfloop collection="#dataSourceObj#" item="unit">
<cfif ucase(dataSourceObj[unit].DRIVER) eq "MSSQLSERVER">
<cfset msSqlList = listAppend(msSqlList,unit) />
</cfif>
<!--- conditions here to add to other lists, etc --->
</cfloop>
</cfif>
<!--- now for presentation --->
<!--- here we can stack the modes in 'real' order --->
<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.