Posts Tagged ‘Search Text in Stored Procedure ( SP )’

Various ways to find particular text within Stored Procedures

Posted: August 29, 2012 by Virendra Yaduvanshi in Database Administrator
Tags: , , , ,
0

So many times we forgot any SQL Statement or text what we had been written in any Stored Procedure, wants to check that Stored procedure name / Statement/Word/Text.

Here are some options, Where we can easily find wanted Text/String within Current Database’s All Stored Procedures.

Option 1:

Select
*
from
Sys.sql_modules
where
definition
like
‘%Your Text Here%’

Option 2:

Select
*
from
Sys.syscomments
where
text
like
‘%Your Text Here%’

Option 3:

Select
*
from
Syscomments
where
text
like
‘%Your Text Here%’

Option 4:

Select
*
from
INFORMATION_SCHEMA.ROUTINES
where
ROUTINE_DEFINITION
like
‘%Your Text Here%’