Content Search

Introduction

Sometime we have situation where we need to search the content of a Stored procedure or Trigger. This article is related to it. Hope it will be informative.

Understanding the Content Search

Suppose we have several Stored procedure in our database and one of them has a specified string and we want to know the name of the Stored Procedure which contains the string. It is impossible to open the content of all the Stored procedure and search one by one.

So How can We Do it ?

It can be done easily by the above script.

BEGIN

      DECLARE @v_SearchCriatria     VARCHAR(max);

      SET @v_SearchCriatria = 'UP'

      SELECT name As [Object Name],

 type_desc As [Object Type],

             create_date As [Date of Creation],

 modify_date As [Date of Modification]

      FROM   sys.procedures

      WHERE  OBJECT_DEFINITION(object_id)
                     LIKE '%'+ @v_SearchCriatria +'%';

END

It’s a simple script and we can understand it easily.

Hope you like it.

Posted by: MR. JOYDEEP DAS