Search Text from Stored Procedure DefinitionPosted: September 29, 2014
You can search text from stored procedure with the following code quickly:
SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%mySearch%' AND ROUTINE_TYPE='PROCEDURE' --Or SELECT [object_id] , [name] , [text] FROM sys.[syscomments] AS sc INNER JOIN sys.[procedures] AS pr ON sc.[id] = pr.[object_id] WHERE sc.[text] LIKE '%myprocedure%'
But you may not find the search word from some procedures you are pretty sure that they are there. Because the Routine_Definition and text from syscomments are nvarchar(4000) in length and the column contains the truncated version of the SP definition.
Arron Bertrand has posting to provide multiple ways to get back the full definition of stored procedures.
Here is the one I grab from his posting and it works pretty good:
SELECT [schema] = OBJECT_SCHEMA_NAME(o.[object_id]), o.name FROM sys.objects AS o INNER JOIN sys.sql_modules AS m ON o.[object_id] = m.[object_id] WHERE m.[definition] LIKE '%mySearch%' AND o.[type] IN ('P')