search for a string in stored procedure -- sql server data mining

sudhakarp80 NO[at]SPAM gmail.com
10/13/2004 4:34:27 PM

hi

I have a very large database with several stored procedures. now, I
need to find a stored procedure that does some calculation if that
stored procedure has a string "MESSAGE"(just an example) in an IF
statement somewhere down the procedure.

currently i am opening every stored procedure, doing a Ctrl+F for the
above string and if i find it then that means i determine that this is
the stored procedure that i will have to work with(change the
calculation in it or some work)

so my question is that can i write something ... that will list me all
the stored procedures which have a certain string contained in them??

I am new to sql stuff .. so please help me. this could save me a lot
of time.

thanks

Dejan Sarka
10/14/2004 11:20:39 AM

Look at this example:

USE Northwind
GO
SELECT SPECIFIC_SCHEMA AS 'Owner', SPECIFIC_NAME AS 'Name',
ROUTINE_DEFINITION AS 'Procedure code'
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE='PROCEDURE' AND
ROUTINE_DEFINITION LIKE '%Sales%'

--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com

[quoted text, click to view]

sql server data mining : search for a string in stored procedure