Hi,
Many a times we change some filed name or a table name in the database. This causes to change all the stored procedure that uses the field. Its can be very easily done if we can get the name of all the stored procedure contains the given text. (It can be bit difficult in case of column name as 2 table can have the same column name. So we need to track it with the help of the table name, unless we are sure that the column name is unique in the database.)
Here is a simple query that gives all the procedure names that contains the given text.
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%String to search%'
AND ROUTINE_TYPE='PROCEDURE'
The following query will give the list of the stored procedure name (along with the text) that contains the given string.
This can be really handy when working with the changes in stored procedure.
Thanks
Vikram