SQL Server 2005: Locating Stored Procedures that Contain a Keyword

Making changes to a database that has been created by a third pary can be a difficult task, especially when you don’t want to break any of the existing stored procedures. You can search the database’s stored procedures for keywords to find which procedures use a keyword that is involved in your change. This makes it much easier in identifying these procedures so you can make the appropriate changes.

1. Open the SQL Server Management Studio.

2. Go to the toolbar and click the New Query button.

3. Select the desired database.

4. Copy, paste and execute the following:

SELECT sys.sysobjects.name, sys.syscomments.text
FROM sys.sysobjects INNER JOIN syscomments
ON sys.sysobjects.id = sys.syscomments.id
WHERE sys.syscomments.text LIKE '%YourKeyword%'
AND sys.sysobjects.type = 'P'
ORDER BY sys.sysobjects.NAME

Where YourKeyword is replaced with the value you are searching for.

The result set will contain the name and text of each stored procedure that contains the given keyword.

courtesy http://www.tech-recipes.com/rx/3701/sql-server-2005-locating-stored-procedures-that-contain-a-keyword/