• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 340
  • Last Modified:

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?
0
dastaub
Asked:
dastaub
1 Solution
 
Amgad_Consulting_CoCommented:
0
 
grossacCommented:
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
 
deightonCommented:
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

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Tackle projects and never again get stuck behind a technical roadblock.
Join Now