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 (bleachedbug@gmail.com) 
 @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>