SQL Server: Search for particular keyword in all procedures (or) Get list of procedures where particular keyword or table name is used

 SQL Server     sbo     Updated on Jan 12, 2014     VIKRAM     526   

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 procedureFunctionsDifference between Stored procedure and FunctionTriggersHow 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:

To search particular keyword in all the functions change the ROUTINE_TYPE = 'FUNCTION'.

  Related Articles
SQL Server: Get List of Stored Procedures with Created Date and Modified Date
SQL Server: Difference between Stored Procedure and Function
SQL Server: Named Batches
  Recent Articles
SQL Server: Get List of Stored Procedures with Created Date and Modified Date
SQL Server: Difference between Stored Procedure and Function
SQL Server: Named Batches