SQL Server Find All Stored Procedures Containing Text

While working with some query I got requirement like find all the stored procedures in database which contains particular text that may be variable name or field name or some 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

I hope it helps you to get all the stored procedures which contains text in sql server.