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%’
WikiDBA 