CFLib.org – Common Function Library Project

Description:
Returns a list of stored procedures containing the string supplied.

Return Values:
Returns a query of stored procedure names that match.

Example:

<cfinclude template="cfStoredProcSearch.cfm">

<cfset qresult = cfStoredProcSearch("Your_Datasource_Name","String_TO_Search")>

<cfoutput query="qResult">
	<font face="verdana" size="2">#qresult.name#</font><br/><br/>
</cfoutput>

Parameters:

Full UDF Source:

<!---
 Search SQL Server Stored Procedures for a value.
 
 @param datasource 	 Database to search. (Required)
 @param searchstring 	 Name to search for. (Required)
 @return Returns a query of stored procedure names that match. 
 @author Jose Diaz (&#98;&#108;&#101;&#97;&#99;&#104;&#101;&#100;&#98;&#117;&#103;&#64;&#103;&#109;&#97;&#105;&#108;&#46;&#99;&#111;&#109;) 
 @version 1, November 14, 2007 
--->
<cffunction name="cfStoredProcSearch" access="public" returntype="query" output=false>

	<cfargument name="datasource" type="string" required="true"/>
	<cfargument name="searchString" type="string" required="true"/>
	<cfset var qStoredProcSearch = "">

	<cfquery name="qStoredProcSearch" datasource="#arguments.datasource#">
		select name
		from sysobjects s
		   , syscomments c
		where s.id = c.id and text like '%#arguments.searchString#%'
	</cfquery>


	<cfreturn qStoredProcSearch />

</cffunction>