How to search text in all database objects in MS SQL Server

Question

I am able search in procedures and triggers for exact text within the "" . But i need to search a string within the %% for all phrases in all database objects

Answer 1

select * from sys.sql_modules 
where definition like '%test%'

some info about SYS.SQL_Modules

Returns a row for each object that is an SQL language-defined module in SQL Server, including natively compiled scalar user-defined function. Objects of type P, RF, V, TR, FN, IF, TF, and R have an associated SQL module

with sp_msfporeachDB, you can test it in all databases;

EXECUTE master.sys.sp_MSforeachdb 'USE [?]; 

if db_id()>4'
Begin
select * from sys.sql_modules where defintion like ''%test%''
end

Answer 2

SELECT ROUTINE_TYPE,* FROM INFORMATION_SCHEMA.ROUTINES WHERE 
CHARINDEX( 'test',ROUTINE_DEFINITION)>0

SELECT VIEW_DEFINITION,* FROM INFORMATION_SCHEMA.VIEWS WHERE 
CHARINDEX('test',VIEW_DEFINITION)>0

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE 
(CHARINDEX( 'col1',COLUMN_NAME)>0 OR CHARINDEX( 'tab2',TABLE_NAME)>0)

Instead of like operator with Percentile (Like '%test%') we used charindex to improve the performance of search