Often I have to search for stored procedures in SQL Server to find pieces of text. The motivation for this is to find procedures which use a table or to look for code which the developer has done silly things like hardcode directories (Boris….). Luckly you can query sys.procedures to generally find the information that you need. Below is an example of query that searches for stored procedures for the word ‘c:\*’.
SELECT Name FROM sys.procedures WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%c:\*%'
T-SQL – Search Stored Procedure Code for Text by Carlos Ferreira, unless otherwise expressly stated, is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported License.