We have to use information_schema in-built Sql Server Object,in my case ACTIVE
is a Row Value which i need to find inside all Stored Procedures.
So,below script will return all Stored Procedure which will contain ACTIVE word.
routine_name as [Procedure],
Routine_Definition [Procedure Code],
specific_schema as [DB Schema],
specific_catalog as [DB Name]
Where routine_definition like '%ACTIVE%'
and routine_type = 'Procedure';