Find Stored Procedures containing a text

Somewhere or Sometimes, we need to Access/Update/Delete all the Stored Procedures which contain some specific text in the name of DataObjects (like SP or Function) or in the definition of DataObjects. In such cases, first we need to get the list of all DataObjects containing the text. Now to get the list of such DataObjects which contain the text in their names, we will write the following query :

 

--#####################################################################################

SELECT      ROUTINE_CATALOG, ROUTINE_NAME, ROUTINE_DEFINITION 
FROM        INFORMATION_SCHEMA.ROUTINES 
WHERE       ROUTINE_NAME LIKE '%NAME%'
            AND ROUTINE_TYPE ='PROCEDURE'
ORDER BY	CREATED

--#####################################################################################
    
 

Here in where clause "ROUTINE_TYPE ='PROCEDURE'" is given to search for Stored Procedures only. Now if you like to search a text 'NAME' in the definition of the DataObjects, then the query will be a bit changed as :

  

--#####################################################################################

SELECT      ROUTINE_CATALOG, ROUTINE_NAME, ROUTINE_DEFINITION 
FROM        INFORMATION_SCHEMA.ROUTINES 
WHERE       ROUTINE_DEFINITION LIKE '%ghost%'
            AND ROUTINE_TYPE ='PROCEDURE'
ORDER BY	CREATED'

--#####################################################################################
    
 

Some other ways to find the same output is as below :

  

--#####################################################################################

SELECT	OBJECT_NAME(OBJECT_ID) AS ObjectName, Definition
FROM	SYS.SQL_MODULES
WHERE	OBJECTPROPERTY(OBJECT_ID, 'IsProcedure') = 1
		AND DEFINITION LIKE '%ghost%'
ORDER BY OBJECT_ID

SELECT	OBJECT_NAME(ID) AS ObjectName, Text
FROM	SYSCOMMENTS 
WHERE	[TEXT] LIKE '%ghost%' 
		AND OBJECTPROPERTY(ID, 'IsProcedure') = 1 
ORDER BY ID

--#####################################################################################
    
 

Reference: Govind Badkur(http://sqlserver20.blogspot.com)