There are times when you want to to list out all the stored procedure names that contains certain keyword or search text.
Here’s a simple query that helps you do that.
Quick way to search within a procedure in SQL Server
SELECT OBJECT_NAME(ID) FROM SysComments WHERE Text LIKE '%<Search String>%' AND OBJECTPROPERTY(id, 'IsProcedure') = 1
Replace the <Search String> with the text that you want to search within all the stored procedures.
Here’s a query that retrieves all the stored procedures that contains the text employee.
SELECT OBJECT_NAME(ID) FROM SysComments WHERE Text LIKE '%Employee%' AND OBJECTPROPERTY(id, 'IsProcedure') = 1