Monday, April 13, 2009

Searching Stored Procedures & Functions based on the text

The below script is used to get the list of Stored Procedures based on their text.

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_DEFINITION LIKE 'Text To Be Searched'


To search for the functions, the ROUTINE_TYPE needs to be changed to FUNCTION as shown below.

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION' AND ROUTINE_DEFINITION LIKE 'Text To Be Searched'


1 comment:

http://pillandia.blogspot.com said...

Interesting indeed!
Best wishes from an Estonian living in Italy