I felt the need to search for a text string in SQL 2005 within a stored procedure.
EASY! yes… but then you get the stored procedure name, and if you want … the text. The text isn’t very useful when it is one giant string now, is it?
So here is a little script I made. It will return the stored procedure name, the line of text where the string was found – and the line number. handy you say……?
DECLARE
@TextString varchar(100)
SET
@TextString = ” ——- YOUR STRING HERE
DECLARE
@ProcName varchar(100)
DECLARE @Results TABLE(ProcName varchar(100), ProcLineNo int, ProcLine varchar(1000))
DECLARE blah CURSOR FOR
SELECT DISTINCT
[Name]
FROM
SysObjects AS O (NOLOCK)
JOIN SysComments AS C (NOLOCK) ON O.Id = C.Id WHERE [Text] LIKE ‘%’+ @TextString +’%’
ORDER BY
[Name]
OPEN blah
FETCH NEXT FROM blah INTO @ProcName
WHILE @@FETCH_STATUS = 0
BEGIN
CREATE TABLE #ProcLines ([lineNo] int IDENTITY(1,1), line varchar(1000))
INSERT INTO
#ProcLines
EXEC sp_helptext @ProcName
INSERT INTO
@Results
SELECT
@ProcName,
[lineNo],
line
FROM
#ProcLines
WHERE
line LIKE ‘%’ + @TextString +’%’
DROP TABLE #ProcLines
FETCH NEXT FROM blah INTO @ProcName
END
CLOSE blah
DEALLOCATE blah
SELECT
*
FROM
@Results
