Search for text in procedures and show context

set nocount on
declare @SearchStr  varchar(100), @Lines  int, @ShowFullProc bit

-- **************************************************************
set @SearchStr = '%SearchText%'
set @Lines = 20
set @ShowFullProc = 0
-- **************************************************************

declare@ProcId  int
 , @ProcChunk  nvarchar(4000)
 , @ProcLine  nvarchar(1000)
 , @PartialLine  nvarchar(1000)
 , @ProcName  sysname
 , @LineNbr  int
 , @ProcChunkLen int
 , @SubStringStart int
 , @SubStringEnd int
 , @CRIdx  int
 , @LFIdx  int
 , @DelimLen  smallint
 , @ProcLineMatch int

declare @ProcIds table
 (
Id  int
 , ProcName sysname
, ProcType varchar(15)
)

declare @ProcLines table
 (
 LineNbr  int
 , ProcLine nvarchar(3800)
 )

INSERT INTO @ProcIds
 SELECT DISTINCT id
, name
, (Case when OBJECTPROPERTY(id, N'IsProcedure') = 1 then 'Procedure'  when OBJECTPROPERTY(id, N'IsScalarFunction') IS NOT NULL then 'Function' else 'Unknown Type' end ) as Type
FROM sysobjects
 WHERE ( OBJECTPROPERTY(id, N'IsProcedure') = 1 OR OBJECTPROPERTY(id, N'IsScalarFunction') IS NOT NULL )
  AND
  id IN ( SELECT DISTINCT id
    FROM syscomments
    WHERE text LIKE @SearchStr
   )

SELECT ProcType, ProcName 
FROM @ProcIds
ORDER BY ProcName

set @PartialLine = ''

declare ProcIds cursor local for
SELECT id, ProcName
FROM @ProcIds
ORDER BY ProcName

open ProcIds
fetch next from ProcIds into @ProcId, @ProcName

while @@FETCH_STATUS = 0
     begin
set @LineNbr = 1
set @ProcLine = ''
 
declare ProcDef cursor local for
SELECT RTRIM(text)
FROM syscomments
WHERE id = (SELECT id
FROM sysobjects
WHERE name = @ProcName)
ORDER BY colid
 
open ProcDef
fetch next from ProcDef into @ProcChunk
 
while @@FETCH_STATUS = 0
     begin
-- Get individual lines into ProcLines table
set @ProcChunkLen = len(@ProcChunk)
set @SubStringStart = 1
set @SubStringEnd = 0
  
while @SubStringStart <= @ProcChunkLen
     begin
-- The typical line break is a CR (13)  and LF (10) but it can also be one or the other 
-- but the sequence should always be CR LF
set @CRIdx = charindex(char(13), @ProcChunk, @SubStringStart)
set @LFIdx = charindex(char(10), @ProcChunk, @SubStringStart)
   
if @CRIdx = 0 and @LFIdx = 0
     begin
set @SubStringEnd = @ProcChunkLen + 1
set @DelimLen = 0
     end
else
     begin
if @CRIdx < @LFIdx
     begin
if @CRIdx = 0
     begin
set @SubStringEnd = @LFIdx
set @DelimLen = 1
     end
else
     begin
set @SubStringEnd = @CRIdx 
      
 if @LFIdx - @CRIdx = 1
 set @DelimLen = 2
 else
set @DelimLen = 1
     end
     end
else
     begin
if @LFIdx = 0
     begin
set @SubStringEnd = @ProcChunkLen + 1
set @DelimLen = 0
     end
else
     begin
set @SubStringEnd = @LFIdx
set @DelimLen = 1
     end
     end
     end

if @SubStringStart > @ProcChunkLen set @SubStringStart = @ProcChunkLen

set @ProcLine = rtrim(substring(@ProcChunk, @SubStringStart, @SubStringEnd - @SubStringStart))
set @SubStringStart = @SubStringEnd + @DelimLen
set @ProcLine = @PartialLine + @ProcLine
set @PartialLine = ''

if @DelimLen = 0 and len(@ProcLine) > 0
     begin
set @PartialLine = @ProcLine
     end
else
     begin
INSERT INTO @ProcLines
(
LineNbr
, ProcLine
)
VALUES
(
@LineNbr
, @ProcLine
)
    
set @LineNbr = @LineNbr + 1
set @ProcLine = ''
     end
     end
  
fetch next from ProcDef into @ProcChunk
     end
 
close ProcDef
deallocate ProcDef

/* Display matching lines and context for currenct proc */declare ProcLineMatches cursor local for
SELECT LineNbr
FROM @ProcLines
WHERE ProcLine LIKE @SearchStr
ORDER BY LineNbr
 
open ProcLineMatches

fetch next from ProcLineMatches into @ProcLineMatch

set rowcount @Lines

while @@FETCH_STATUS = 0
     begin
print @ProcName + ' (at line ' + cast(@ProcLineMatch as varchar) + ')' + char(13) + char(10) + '================================================'

SELECT LineNbr
, ProcLine
FROM @ProcLines
WHERE LineNbr > @ProcLineMatch - (@Lines / 2)
ORDER BY LineNbr

print ''

fetch next from ProcLineMatches into @ProcLineMatch
     end

close ProcLineMatches
deallocate ProcLineMatches

set rowcount 0

/* If desired, show the entire stored procedure. */if @ShowFullProc = 1
     begin
SELECT LineNbr
, ProcLine
FROM @ProcLines
ORDER BY LineNbr
     end

delete @ProcLines

fetch next from ProcIds into @ProcId, @ProcName
     end

close ProcIds
deallocate ProcIds