How to find list of stored procedure with content info in SQL Server

This post describes the how can you find list of stored procedure with specific text content or how to answer following question.

• List All Stored Procedure, Function, Views, Triggers which uses a Update or Insert or Delete Statement.
• List All Stored Procedure, Function, Views, Triggers which uses temporary table.
• List All Stored Procedure, Function, Views, Triggers which uses a particular table.
• and Many More.

There is a system table which contains information about stored procedure,view and trigger contents “syscomments”

List All Stored Procedure, Functions, Views, Triggers which uses “Update, Insert or Delete Statement”

Select Distinct A.Name,A.type_desc 
from SysComments B
Join sys.all_objects A On B.id=A.object_id
where text Like '%update %' or text like '%insert %' or text like '%delete %'

List All Stored Procedure, Functions, Views, Triggers which uses temporary table.

Select Distinct A.Name,A.type_desc 
from SysComments B
Join sys.all_objects A On B.id=A.object_id
where Replace(Text,' ','') Like '%Table#%'

List All Stored Procedure, Functions, Views, Triggers which uses a particular table.

Select Distinct A.Name,A.type_desc 
from SysComments B
Join sys.all_objects A On B.id=A.object_id
where text like '%Sys.Objects%'

List All Stored Procedure , Functions, Views, Triggers which contains a specific text

Select Distinct A.Name,A.type_desc 
from SysComments B
Join sys.all_objects A On B.id=A.object_id
where text like '%Data%'

So “syscomments” is really helpful.

NOTE: Searching cannot be done when stored procedure function or View Are Encrypted.