SQL Server Find All Stored Procedures Containing Text

Introduction:

Here I will explain how to find all stored procedures containing text in sql server or how to search text in stored procedures in sql server or find all the stored procedures having given text or column name or table name in sql server. To get / find all the stored procedures which contains give text in sql server we need to write query with objectproperty values using syscomments table.

Description:

In previous articles I explained sql server group by alias column name, sql server convert string to proper case / camel case, sql server split string with comma separated delimiter, sql server add hours / minutes to current date using DateAdd function, SQL Server single procedure to insert update delete, Primary key constraint in sql server, foreign key constraint in sql server and many articles relating to SQL server. Now I will explain how to find all stored procedures which contains text in sql server.

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 server system 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.

If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

Subscribe by RSS Subscribe by Email