Solved

SQL search thru Stored Procedures

Posted on 2011-03-07
5
313 Views
Last Modified: 2012-05-11
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
Comment
Question by:dastaub
5 Comments
 
LVL 4

Expert Comment

by:Amgad_Consulting_Co
ID: 35058025
0
 
LVL 2

Accepted Solution

by:
grossac earned 500 total points
ID: 35058041
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
 
LVL 18

Expert Comment

by:deighton
ID: 35058046
script out the database into SQL, then search the SQL script and get the sp name from the create statement
0
 

Expert Comment

by:Amit_Kohli
ID: 35058349
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
 

Author Closing Comment

by:dastaub
ID: 35058479
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL STANDARD CORE 8 38
backup and restore 21 30
SSRS: Why is Visual Studio stripping these properties? 2 23
SQLCMD Syntax 2 15
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question