How to search in 100 stored procedures

Question

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: 246979 More actions · Answer 4

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

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel