SQL search thru Stored Procedures

Is there a way to search the text of all stored procedures?  Many times I am about to write a SP and I remember writing similar code, but I do not remember the name of the SP that contains the code.  If I could search all SP's, I could find the code I am about to write.

Is there a 'search" feature for searching thru all stored procedures in SQL 2005?
dastaubAsked:
Who is Participating?
 
grossacConnect With a Mentor Commented:
This is what I've been using and it works great.


CREATE PROCEDURE [dbo].[SP_UTIL_FindTextInDatabase]
    -- Add the parameters for the stored procedure here
@text nvarchar(1000)

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
declare @newString varchar(205)
Set @newString = '%' + @text + '%'
    -- Insert statements for procedure here
	SELECT
		o.name, 
		SUBSTRING(c.text, CHARINDEX(@text, c.text)-50, 100) as Excerpt,
		c.text
	FROM
		sysObjects o INNER JOIN
		sysComments c ON o.id = c.id
	WHERE
		o.id in (select id from syscomments where text like @newString)
	ORDER by name
END

Open in new window

0
 
Amgad_Consulting_CoCommented:
0
 
deightonprogCommented:
script out the database into SQL, then search the SQL script and get the sp name from the create statement
0
 
Amit_KohliCommented:
Right click the database node, click on Tasks, Generate scripts - this will display a wizard to let you choose the objects you want to script, to new file or query window, etc.
Hope this helps.
0
 
dastaubAuthor Commented:
copied and pasted and it worked.  I changed the SP name to FS so i can type exec FS 'string here' and run.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.