Know Dot Net Article

Search Stored Procedures with SQL in SQL Server

Find Text or Name of Stored Procedure

by Les Smith

Ever need to find all stored procedures that have certain text in them?  Ever need to find all stored procedures that have a specified text in the procedure name?  I have!.  Numerous times I have need to find any and all stored procedures that have a certain text.  I have also 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.

If you have comments on any of my articles, or want to ask a question, contact me on my blog at Les's Blog.