How do I find a stored procedure containing <text>?

Question

I need to search a SQL server 2008 for stored procedures containing where maybe the name of a database field or variable name.

Answer 1

SELECT ROUTINE_NAME, ROUTINE_DEFINITION     FROM INFORMATION_SCHEMA.ROUTINES      WHERE ROUTINE_DEFINITION LIKE '%Foo%'      AND ROUTINE_TYPE='PROCEDURE' 

---

SELECT OBJECT_NAME(id)      FROM SYSCOMMENTS      WHERE [text] LIKE '%Foo%'      AND OBJECTPROPERTY(id, 'IsProcedure') = 1      GROUP BY OBJECT_NAME(id) 

---

SELECT OBJECT_NAME(object_id)     FROM sys.sql_modules     WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1     AND definition LIKE '%Foo%' 

Answer 2

Grab yourself a copy of the free Red-Gate SQL Search tool and start enjoying searching in SQL Server! :-)

It's a great and very useful tool, and YES! it's totally, absolutely FREE for any kind of use.