Urgent:search for a key word.

Question

Post reply

Urgent:search for a key word.

HemaSunder Bugatha

Right there with Babe

Points: 737
More actions
March 8, 2006 at 3:40 am

#170831

Hi,
I want to search for the stored procedures which are using a particular keyword.Can anyone suggest me how to write a query for that?

..hema

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Tyson L. Price Mr or Mrs. 500 Points: 599 More actions · Answer 1

Run in the desired data base from Query Analyzer:

declare @s varchar(255)

set @s-2 = 'Text to Search Here'

DECLARE @msg varchar(255) ,

@ul varchar(255)

select @s-2='%' + @s-2 + '%'

select 'SP Name'=upper(o.name),

Seq=colid ,

'SP Line'=substring(text,patindex(@s,text)-5, 30)

from syscomments c ,

sysobjects o

where o.id=c.id

and patindex(@s,text) > 0

order by name

SELECT @msg='* Stored procedures containing string "' + @s-2 + '=' +

convert(varchar(8),@@rowcount) + ' *'

SELECT @ul=replicate('*',datalength(@msg))

Print ' '

PRINT @ul

PRINT @msg

Print @ul

GO

HemaSunder Bugatha Right there with Babe Points: 737 More actions · Answer 2

I want to know whether there is any

predefined system stored procedure for finding the same and thanx for the reply...

..hema

Lowell SSC Guru Points: 323518 More actions · Answer 3

nothing predefined, but you can easily take Tyson's example and create a stored proc witht eh same code.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!