search for text in all your stored procedures

admin — Thu, 05 Jun 2008 04:08:47 GMT

-- create temporary Result table to gather names and text
-- of the procedures in the DataBaseName database :
CREATE TABLE #Result
(TextField varchar(max), ProcName varchar(100))

-- create temporary ProcName table with the names of
-- all the procedures in the database [DataBaseName]:
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
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

SQL Scripts

search for text in all your stored procedures