How to search in 100 stored procedures

Question

Post reply

How to search in 100 stored procedures

Shadab Shah

SSCarpal Tunnel

Points: 4759
More actions
August 5, 2012 at 10:31 pm

#265315

Hi ,
Suppose i have 100 stored procedures. Now i want to search for 'abc' in all the 100 Stored Procedure. How can i perform that task.

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

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

Suresh B. SSC-Insane Points: 22986 More actions · Answer 1

use <database>

go

select * from sys.sql_modules

where definition like '%abc%'

Kivan SSC Enthusiast Points: 120 More actions · Answer 2

Try with the following

Use "Your-DBNAME"

GO

SELECT DISTINCT o.name ,o.xtype

FROM syscomments c

INNER JOIN sysobjects o ON c.id=o.id and o.type='p'

WHERE c.TEXT LIKE '%SEARCH_TEXT%'

PS: type='p' is to retrieve only stored procedures from sysobjects.

Thanks

Kivan 😉

laurie-789651 SSCertifiable Points: 7680 More actions · Answer 3

You can create a script of all stored procedures in a database & search through that. It's easy to see the context & skip from one proc to another.

If you start somewhere new it can be a handy way of finding how/where particular tables & functions are used.

Right-click on the database, select Tasks, select Generate Scripts - then choose all procedures.

Phil Parkin SSC Guru Points: 246695 More actions · Answer 4

Download Redgate's free SQL Search product & use that - it's very intuitive.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.