Post
Ask Question

Queries for search Procedure, Function, View and Trigger in SQL Server using any keyword

Introduction

This article shows how we can search any Procedure, Function, View and Trigger. Suppose we have created a Stored Procedure and we forgot name of that SP. Then we can search that SP with any keyword which we used in that.

Search Stored Procedures

Here is the Query to get SP which contain “booking”:

  1. selectroutine_definitionas'Script',routine_nameas'Name'frominformation_schema.routines  
  2. whereroutine_definitionLike'%booking%'androutine_type='PROCEDURE';  

Output



Figure 1: output

The Above Query will return all Stored Procedures which contain “booking” keyword.

Search Functions

For Search function in DB. Only you need to do change routing_type.

i.e. routine_type='FUNCTION';

Ex

  1. selectroutine_definitionas'Script',routine_nameas'Name'frominformation_schema.routineswhereroutine_definitionLike'%booking%'androutine_type='FUNCTION';  
Search Views :

Query to get all view list which contain “booking” in it.
  1. selectroutine_definitionas'Script',routine_nameas'Name'frominformation_schema.routineswhereroutine_definitionLike'%booking%'androutine_type='FUNCTION';
Search Trigger

Query to get all Triggerlist which contain “booking” in it.
  1. SELECTview_definitionas'Script',table_nameas'Name'FROMINFORMATION_SCHEMA.VIEWSWHEREview_definitionLike'%booking%';