Technical Article

SP to search String in TEXT of SPs

,

Well this is an addendum to my earlier post for searching a specific string in all the Stored Procedures defined in a database and returning the name of the stored procedure in which it is contained. This is optimized based on suggestion of Greg and I thank him for his advise.

CREATE PROCEDURE SEARCHFORSTRING @SEARCHSTRING VARCHAR(100)
AS
SELECT DISTINCT(sysobjects.name)

        FROM 
sysobjects,syscomments

WHERE 
sysobjects.id =     syscomments.id

AND 
sysobjects.type = 'P'
AND 
sysobjects.category=0
AND
CHARINDEX(@SEARCHSTRING ,syscomments.text)>0

Rate

4 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

4 (1)

You rated this post out of 5. Change rating