Find All The StoredProcedures Containing A Text in SQL Server

Introduction

In this article i will explain about How to find all the Stored Procedures containing a specific Text. 

Practice

Here i am writing my Stored Procedure to find all the Stored Proceduresin database which contains that particular text .It may be field name or variable name.

To get all stored procedures which contains text in sql server we have different ways by using sql server system modules like syscomments or sys.sql_modules we can get all the stored procedures which contains particular text in sql query statements.

By using  sql_modules 

SELECT OBJECT_NAME(object_id)

FROM sys.sql_modules

WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1

AND definition LIKE '%insupd%'

Here i searched "insupd"  text in all procedures in my database, you just need to change the name which  used in Like '%Your_Text%', and all the related SP will be displayed you in a sql query result window.  

By using system modules

SELECT OBJECT_NAME(id)

FROM SYSCOMMENTS

WHERE [text] LIKE '%insupd%'

AND OBJECTPROPERTY(id, 'IsProcedure') = 1

GROUP BY OBJECT_NAME(id)