Search text in stored procedure

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

SQL

search

, +

Hi All,

I would like to search text in stored procedure(Sqlserver2005).

I can do this with the following script.

SQL

SELECT name
FROM   sys.procedures
WHERE  Object_definition(object_id) LIKE '%Order%'

But, It is searching in commented lines also. I do not want to search commented lines of stored procedure.

Please help me!

Posted 8-Jul-14 19:50pm

Prasad Avunoori

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

vani suresh · Answer 1 · 2014-07-08T22:44:00

Solution 2

Hi Prasad,

Check this below query it works for me and tested also.

SQL

SELECT name
FROM   sys.procedures
WHERE  Object_definition(object_id) LIKE '%Order%' and
 Object_definition(object_id) NOT LIKE '%/**%'

Let me know if it is not work...

Posted 8-Jul-14 22:44pm

vani suresh

Comments

Prasad Avunoori 9-Jul-14 5:56am

No, Vani. It also didn't work for me.

Because it filters the stored procedures which have /*.

KaushalJB · Answer 2 · 2014-07-08T20:53:00

Solution 1

This link might lead to the solution :
Search Text in Stored Procedure in SQL Server
using escape

Posted 8-Jul-14 20:53pm

KaushalJB

Comments

Prasad Avunoori 9-Jul-14 3:13am

Thank you. But, i don't want to search commented text.

Gauri Chodanker · Answer 3 · 2014-07-09T04:33:00

Solution 3

Try using the below query

SQL

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFOMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE ('%YOURSEARCHTEXT%')

Posted 9-Jul-14 4:33am

Gauri Chodanker