Search Text in a Sub Procedure in SQL Server

Find Text in sub procedures SO.Type = ‘P’ means Procedure, other system object types are:

P – Procedures

FN – Scalar-Valued Functions

TF – Table-valued functions

V – Views

D – Database Diagrams

X – Tables

 

I have commented that line in bold and it will search everything for entered string value in this case.

 

To search text in a sub-procedure or otherwise

 

CREATE PROCEDURE [dbo].[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

 

To search for a string within subprocedure name:

 

CREATE PROCEDURE [dbo].[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

 

-SB