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
