CREATE proc spgetSPText(@sp_name sysname) as
SET NOCOUNT ON
declare C1 cursor for 
     SELECT sc.text
     FROM sysobjects so 
     INNER JOIN syscomments sc ON so.id = sc.id 
     WHERE so.xtype = 'P' 
     ORDER BY sc.colid
declare @sp_line nvarchar(4000), @ptr varbinary(16)
select cast(N'' as ntext) as sp_text into #temp
select @ptr = textptr(sp_text) from #temp
open C1
fetch C1 into @sp_line
while @@fetch_status = 0
begin
     updatetext #temp.sp_text @ptr null 0 @sp_line
     fetch C1 into @sp_line
end
close C1
deallocate C1
select sp_text from #temp
GO

create proc spgetSPText(@chvSearchtext varchar(50)) as
SET NOCOUNT ON
     SELECT DISTINCT so.name into #temp
     FROM sysobjects so
     INNER JOIN syscomments sc ON so.id = sc.id
     WHERE 
       (
       so.xtype = 'TR' or
       so.xtype = 'FN' or
       so.xtype = 'P' or
       so.xtype = 'X' or
       so.xtype = 'TF'
       )
     and sc.text like'%' + @chvSearchtext + '%'
     ORDER BY name    
   select name from #temp

drop table #temp
SET NOCOUNT Off
GO


exec spgetSPText 'sales'

DROP PROCEDURE spgetSPText