select distinct object_name( id ) from syscomments where text like '%ваш код%'

SELECT OBJECT_SCHEMA_NAME(object_id) AS [Schema], OBJECT_NAME(object_id) AS [Object] FROM
                       sys.sql_modules AS S WHERE S.definition LIKE '%search pattern%'

SELECT name
FROM sysobjects
WHERE type='P' AND OBJECT_DEFINITION(object_id) LIKE N'%> FIRST[_]DATE%';

SELECT name
FROM sysobjects
WHERE type='P' AND OBJECT_DEFINITION(object_id) LIKE N'%> FIRST[_]DATE%';

SELECT name
FROM sys.objects
WHERE type='P' AND OBJECT_DEFINITION(object_id) LIKE N'%> FIRST[_]DATE%';

if exists(select * from sys.objects where name='pFindText') drop proc pFindText
go
create proc pFindText
@vFind varchar(max),
@iItem int=1
as set nocount on begin try
--
declare @objs table (ID int, name sysname, type sysname, Ord int identity)
insert  @objs select ID, name, type from
 (
  select ID, name, type,
   case type 
    when 'V'  then 0
    when 'P'  then 1
    when 'IF' then 2 
    when 'TF' then 3
    when 'FN' then 3
              else 4
   end Ord from 
   (
    select Obj.ID, name, type from sys.syscomments Cmm join sys.sysobjects Obj on Cmm.ID=Obj.ID where text like '%'+@vFind+'%'
   ) Qry
     group by ID, type, name
 ) Qry
   order by Ord

select * from @objs

declare @temp table (txt varchar(max), ID int identity)
insert  @temp select text from sys.syscomments where id=(select ID from @objs where Ord=@iItem)

declare @int int select @int=min(ID) from @temp
declare @max int select @max=max(ID) from @temp
declare @vText varchar(max) set @vText=''
while @int<=@max
 begin
 select @vText=@vText+txt from @temp where ID=@int
 set @int=@int+1
 end
print @vText
--
end try begin catch select error_message() end catch
go

--example
exec pFindText 'declare', 1
go