List all stored procedures with particular Text to Find.

There comes a scenario when you want to list all stored procedures with particular Text.

Here is the Script to List all the Objects which contains that text(even in comments) in that Particular Database:

SELECT DISTINCT

       o.name AS Object_Name,

       o.type_desc,o.create_date,o.modify_date

  FROM sys.sql_modules m

       INNER JOIN

       sys.objects o

         ON m.object_id = o.object_id

 WHERE m.definition Like '%coalesce%'

Here is the Script to List all the Objects which contains that text(even in comments) in all Database:

EXEC sp_msforeachdb  'USE [?];  SELECT DISTINCT

       o.name AS Object_Name,"?" as DBName,

       o.type_desc,o.create_date,o.modify_date

  FROM sys.sql_modules m

       INNER JOIN

       sys.objects o

         ON m.object_id = o.object_id

 WHERE m.definition Like ''%coalesce%'';'

Note: Here We had used System Stored Procedure sp_msforeachdb 

Happy Coding !!!