Have an application with 100′s of stored procs and need to find one?  We use this code below that searchs all the stored proc’s syntax in a SQL database.

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

Author

RBA Author

Leave a Reply