Find All Stored Procedures Containing Text

The article explain how to find all the stored procedures containing text 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 use SQL server system modules like syscomments or sys.sql_modules. These modules are used to get all the stored procedures which contains particular text in sql query statements.

To get stored procedures which contains text in sql server use the query as given below.

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

On executing the above query it will return all the stored procedures which contains text like “name”. The output will be as follows.

Output

Output of Stored Procedures Containing Text 1

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 '%name%'

The above query is used to search for “name” in complete database to get all the stored procedures which contains in sql server.

Output

Output of Stored Procedures Containing Text 2