Search Text in Stored Procedure in SQL Server

Search Text in Stored Procedure in SQL Server

Hello everyone, I am going to share the query for search a text from my all database stored procedures with and without system stored procedures.

#Declare a input text as given below.

DECLARE @SEARCH_TEXT VARCHAR(255)
SET @SEARCH_TEXT=anil.singh@gmail.com'

#Query for Search Text in Stored Procedure without system Procedure

SELECT DISTINCT
      OBJ.NAME AS OBJECT_NAME,
      OBJ.TYPE_DESC
      FROM SYS.SQL_MODULES MOD
              INNER JOIN SYS.OBJECTS OBJ ON MOD.OBJECT_ID = OBJ.OBJECT_ID
              WHERE MOD.DEFINITION LIKE '%'+@SEARCH_TEXT+'%'
              ORDER BY TYPE_DESC

                                                 #OR

#Query for Search Text in Stored Procedure with system Procedure.

SELECT DISTINCT
      OBJ.NAME AS OBJECT_NAME,
      OBJ.TYPE_DESC
      FROM SYS.SQL_MODULES MOD
              INNER JOIN SYS.OBJECTS OBJ ON MOD.OBJECT_ID = OBJ.OBJECT_ID
              WHERE MOD.DEFINITION LIKE '%['+@SEARCH_TEXT+']%'
              ORDER BY TYPE_DESC



ANIL SINGH

Anil Singh is an author, tech blogger, and software programmer. Book writing, tech blogging is something do extra and Anil love doing it. For more detail, kindly refer to this link..

My Tech Blog - https://www.code-sample.com/
My Books - Book 1 and Book 2

Search Text in Stored Procedure in SQL Server Search Text in Stored Procedure in SQL Server Reviewed by Anil Singh on 11:23 PM Rating: (5)
www.code-sample.com/. Powered by Blogger.
^