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=[email protected]'

#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 Blogs - https://code-sample.com and https://code-sample.xyz
My Book1 - BEST SELLING ANGULAR BOOK (INCLUDING ALL VERSIONS 2, 4, 5, 6, 7)
My Book2 - ANGULAR 2 INTERVIEW QUESTIONS BOOK - Both Books are Available on WorldWide.

www.code-sample.com/. Powered by Blogger.