Query to look for text in procedures

This is a query that I use every day! Say, you've changed a datatype of a field, or renamed it, and now you need to change it everywhere where it's been called from procedures. Or you have hardcoded something (or someone before you did it), and now you need to change it or, better, get rid of it. Anyway, you will have to look for some set of symbols in all your procedures. There are, actually, quite a few samples of how to do this on the Internet, this is just one of them:


USE [YourDataBaseName]
GO
-- create temporary Result table to keep names and text of the procedures in the database :
CREATE TABLE #Result
(TextField varchar(max), ProcName varchar(100))

-- create temporary ProcName table with the names of all the procedures in the database:
CREATE TABLE #ProcList
(ID int IDENTITY, ProcName varchar(100))

-- populate the ProcName table with the procedure names:
INSERT #ProcList SELECT [name] from sys.procedures

-- get the number of procedures (to be used in the loop below):
DECLARE @NumberOfProcs int
SELECT @NumberOfProcs = COUNT(*) FROM sys.procedures

-- loop to populate the Result table:
DECLARE @i INT
SET @i = 1
DECLARE @ProcName varchar(100)
DECLARE @SQL varchar(2000)
WHILE @i <= @NumberOfProcs
BEGIN
SELECT @ProcName = ProcName FROM #ProcList WHERE ID = @i
-- and ProcName like '%proc name%'
SET @SQL = 'INSERT INTO #Result (TextField) EXEC sp_helptext ' + @ProcName
EXEC (@SQL)
UPDATE #Result SET ProcName = @ProcName WHERE ProcName IS NULL
SET @i = @i + 1
END

-- look for a string you need [your string] in the Result table
SELECT * FROM #Result WHERE TextField LIKE '%[your string]%'

-- clean up
DROP TABLE #Result
DROP TABLE #ProcList

--
Additional observations.
1. The general idea is to use the sp_text procedure.
2. Now, Pinal Dave, who runs a very useful blog on SQL server, has something much shorter for this (and guess what, it works just the same, even faster :) ):

USE [YouDatabaseName]
GO
SELECT DISTINCT o.name ,o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%[your string]%'