Time and time again I find the need to search all stored procedures for a table name or text in SQL server.
Below is snippets of code that will do this task for you:
SELECT OBJECT_NAME(object_id), OBJECT_DEFINITION(object_id) FROM sys.procedures WHERE OBJECT_DEFINITION(object_id) LIKE '%TEXT TO SEARCH FOR%'
SELECT DISTINCT o.name, o.xtype FROM syscomments c INNER JOIN sysobjects o ON c.id=o.id WHERE c.TEXT LIKE '%INSERT TEXT HERE%'
There are also additional tools that you could use to search SQL Server with:
Redgate’s SQL Search is a free plugin for SSMS that you could use to search across multiple object types and multiple databases
ApexSQL Search – Search SQL Server data and objects for free in SSMS and Visual Studio
SSMS Tools pack – Has the same search functionality as previous tools with some added extras