Searching through stored procedures

[mike509123]

Hi,
I am currently using Microsoft SQL Server Management Studio to look at the contents of my database and view/edit my stored procedures. Is there a way using SQL Server Management Studio to search through all the stored procedures for the words 'accounts'. I know one solution i have is to manually right click on each stored procedure and select "modify" and then manually inspect each stored procedure. Is there a better way?

 

[AlexCuse]

The INFORMATION_SCHEMA views will be helpful for things like this. I suggest you read as much as you can about them.

Here's an idea:

[COLOR=blue]select[/color] ROUTINE_NAME
	, ROUTINE_DEFINITION
[COLOR=blue]from[/color] INFORMATION_SCHEMA.ROUTINES
[COLOR=blue]where[/color] ROUTINE_TYPE = [COLOR=red]'PROCEDURE'[/color]
	and ROUTINE_DEFINITION like [COLOR=red]'%account%'[/color]

Hope it helps,

Alex

[small]----signature below----[/small]
Now you can go where the people are one!
Now you can go where they get things done!

 

[nice95gle]

Just to add to what Alex is saying, you should also do some reading on the different wildcard characters and how to use them in searches.

Well Done is better than well said
- Ben Franklin

 

[TysonLPrice]

I use this to serach for keys words. I don't remember where I got it:

declare	@s varchar(255) 
set @s = 'Text to find'
DECLARE	@msg varchar(255) ,
		@ul varchar(255)
select @s='%' + @s + '%'
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 + '=' + 
convert(varchar(8),@@rowcount) + ' *'
SELECT @ul=replicate('*',datalength(@msg))
Print ' '
PRINT @ul
PRINT @msg
Print @ul