SQL SERVER – Query to Search Anything Inside Stored Procedures From a DataBase

Search Anything Inside Stored Procedures

 -- =========================

 -- SEARCH INSIDE PROCEDURES

 -- =========================

 SELECT DISTINCT

    A.NAME

 FROM SYS.OBJECTS A, SYSCOMMENTS B

 WHERE

 A.OBJECT_ID = B.ID

 AND [TEXT] LIKE '%InputText%'

Search Stored Procedures Only From Specific Table(s)

We can filter further by using table, that store procedure depends.

 -- ==================================================

 -- SEARCH STORED PROCEDURES FROM SPECIFIC TABLE ONLY

 -- ==================================================

 SELECT DISTINCT A.NAME

 FROM SYS.OBJECTS A, SYSCOMMENTS B

 WHERE

 [TEXT] LIKE '%InputText%'

 AND A.OBJECT_ID = B.ID

 AND A.OBJECT_ID

 IN

 (

    SELECT DISTINCT OBJECT_ID 

    FROM SYS.SQL_DEPENDENCIES 

    WHERE referenced_major_id IN (object_id('TableName'))

 )