How to find whether particular query statement exist in which procedure

Question

Post reply

How to find whether particular query statement exist in which procedure

Nisha N.Kumar

Ten Centuries

Points: 1100
More actions
April 14, 2008 at 4:18 am

#189025

Hi All,
I need solution for the following :
I have several stored procedures in a database.
For example, consider I am having 50 procedures in a database.
I need to know in which procedure a particular statement exists.
It may be one sproc or in many sprocs.
For example : If any of my sproc contains the following query in it
Select distinct orderid from order;
I need to know which all sproc contains the above statement.It should show all the sprocs in which particular query statement appears.
How to get this
Thanks in advance,
Nisha

Viewing 9 posts - 1 through 8 (of 8 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Nisha

The information you're looking for is in the sys.sql_modules system view.

John

Nisha N.Kumar Ten Centuries Points: 1100 More actions · Answer 2

John,

Can you give me the query for executing this...

Thanks

Nisha

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Nisha

I can, but I won't. Look up sys.sql_modules in Books Online, then try to write the query yourself. If you're stuck on anything particular then post back and we'll try to help you out. But doing all the work for you... that would just be too easy and you wouldn't learn anything.

Good luck

John