I have used this. This will help us to ignore the commented lines as well as the line prior to the CREATE statetments. Look like conventional but helpful

--Exec dbo.search 'test'

Create Procedure [dbo].[search] (@searchstr varchar(100))

As

Declare @cntr int

Declare @cnt int

Declare @proc_name varchar(100)

Declare @proc_type varchar(100)

Declare @seq int

Declare @out_str varchar(255)

Create Table #proc_names (sl_no int identity(1,1), proc_name varchar(100),type varchar(100))

Insert into #proc_names

Select name,Case xtype When 'P' Then 'Stored Procedure'

When 'V' Then 'View'

When 'TR' Then 'Trigger'

When 'FN' Then 'Function' End

from dbo.sysobjects where xtype in ('P','V','TR','FN') and uid = 1

Create Table #proc_script (seq_no int identity(1,1), proc_str text)

Create Table #Rtn_proc_str (sl_no int identity(1,1), object_name varchar(100), object_type varchar(100), errortext varchar(255))

Set @cntr = 1

Select @cnt = count(1) from #proc_names

While @cntr <> @cnt

Begin

Select @proc_name = proc_name, @proc_type = type from #proc_names where sl_no = @cntr

BEGIN TRY

Insert into #proc_script

Exec dbo.sp_helptext @proc_name

END TRY

BEGIN CATCH

Set @out_str = ERROR_MESSAGE()

Insert into #Rtn_proc_str

Values (@proc_name, @proc_type, @out_str)

END CATCH

Select @seq = seq_no from #proc_script where proc_str like 'Create %'

Delete from #proc_script where seq_no < @seq

Delete from #proc_script where proc_str like '--%'

If exists (Select 1 from #proc_script where proc_str like '%'+@searchstr+'%')

Begin

Insert into #Rtn_proc_str

Values (@proc_name, @proc_type,'')

End

Truncate Table #proc_script

Set @cntr = @cntr + 1

End

Select object_name, object_type, errortext from #Rtn_proc_str order by object_type, object_name