Find Text or Name of Stored Procedure in SQL Server

SQL Developer needs to find all stored procedures that have a specified text in the procedure name.
Sometime we needed to find procedure names that have a certain text in their names. This article will give you the SQL statements for doing just that.

The following stored procedure will list all stored procedure names whose text contains the parameter search string.

CREATE PROCEDURE Find_Text_In_SP
@StringToSearch varchar(100)
AS
SET @StringToSearch = '%' +@StringToSearch + '%'
SELECT Distinct SO.Name
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
AND SO.Type = 'P'
AND SC.Text LIKE @stringtosearch
ORDER BY SO.Name
GO

The following stored procedure list all stored procedure names whose text contains the parameter search string.

CREATE PROCEDURE Find_SPName_With_Text
@StringToSearch varchar(100)
AS
SET @StringToSearch = '%' + @StringToSearch + '%'
SELECT DISTINCT SO.NAME
FROM SYSOBJECTS SO (NOLOCK)
WHERE SO.TYPE = 'P'
AND SO.NAME LIKE @StringToSearch
ORDER BY SO.Name
GO

You can also use the code of the stored procedures in Query Analyzer instead of making a stored proc out of the code shown above.