Find All Stored Procedures Containing Text in SQL Server

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

To get stored procedures which contains text in sql server we need to write the query like as shown below

SELECT OBJECT_NAME(id) FROM SYSCOMMENTS WHERE [text] LIKE '%categorytitle%' AND OBJECTPROPERTY(id, 'IsProcedure') = 1 GROUP BY OBJECT_NAME(id)

When we run above query it will return all the stored procedures which contains text like “categorytitle” and output will be like as shown below

In another way by using sql_modules in sql server we can get all the stored procedures which contains particular text like as shown below.

SELECT OBJECT_NAME(object_id) FROM sys.sql_modules WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1 AND definition LIKE '%categorytitle%'

If you observe above query we are searching for “categorytitle” and in complete database to get all the stored procedures which contains in sql server. Now we will run and see the output that would be like as shown below