Sometimes, especially on very old applications that have gone through several rewrites but are still using the original database, I find myself wondering which stored procedures reference a given table, or each other, or whether changing the name of a view or column name will break something somewhere in the database.  There are some tools out there to help this kind of thing, such as Red Gate's Refactor tool, but at a simpler level if you just need to search your stored procedures for a particular string, you can do it using this query that I just saw come across the Sql Server SQL list on SQL Advice:

SELECT ROUTINE_NAME, ROUTINE_DEFINITION 

FROM INFORMATION_SCHEMA.ROUTINES 

WHERE ROUTINE_DEFINITION LIKE '%lq_Campaign%' 

AND ROUTINE_TYPE='PROCEDURE'