Various ways to find particular text within Stored Procedures

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

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s