How to search for particular keyword in all procedures

Introduction:

In this article i will explain how to search for particular keyword in all procedures (or) Get list of procedures where particular keyword or table name is used in SQL Server.

Description:

In previous articles i explained about Stored procedure, Functions, Difference between Stored procedure and Function, Triggers, How to get list of stored procedures with Created Date and Modified Date, and Get List of SQL Jobs in SQL Server. Now i will explain how to search for particular keyword in all procedures (or) Get list of procedures where particular keyword or table name is used in SQL Server.

To search for particular keyword in all procedures we need to write query as follows:

Query:

select    ROUTINE_NAME as [Procedure Name], 
          ROUTINE_DEFINITION as [Procedure Description], 
          ROUTINE_CATALOG as [Database Name], 
          CREATED as [Created Date], 
          LAST_ALTERED as [Modified Date]
from      INFORMATION_SCHEMA.ROUTINES
where     ROUTINE_TYPE = 'PROCEDURE'
and       ROUTINE_DEFINITION like '%print%'

The above query will return all the stored procedures where print keyword is used.

Note:

Similarly we can pass tabel name to get all the stored procedures where particular table name is used.

Output: