Search SQL Server Stored Procedures for any text string


Here’s the SQL script I probably use most when I’m trying to work out dependencies and scope of development changes.  Query Analyzer allows you to search objects (hit F4 – it’s cool!) and find objects by ‘name’, but there’s no obvious way to search stored procs for a particular string.


SQL Server hides Stored Procedure code in the sysComments table (in each database), and so it’s possible to search.  This is another script I found from not sure where (so can’t really take credit).  It does a great job of scanning and even gives you line numbers.  It would be great to be able to just shove this into the master database, but you need to be in the context of the database you’re searching in (or it would have to be amended somewhat to run some more ‘dynamic’ SQL). 


I have previously front-ended this for development databases with an ASP.NET page that simply runs the query substituting a parameter for the search text and connecting to the correct database.  The results show nicely in a grid.


Here’s the code – just paste into a new query in your chosen database, change @SearchTerm and away you go…


 


SET NOCOUNT ON
GO

declare 
@dbname sysname
,@SearchTerm    varchar(100)
,@BlankSpaceAdded   
int
,@BasePos       int
,@CurrentPos    int
,@TextLength    int
,@LineId        int
,@AddOnLen      int
,@LFCR          int –lengths of line feed carriage return
,@DefinedLength int
,@obj_name    varchar(255)
,@prev_obj_name 
varchar(255)


/* NOTE: Length of @SyscomText is 4000 to replace the length of
** text column in syscomments.
** lengths on @Line, #CommentText Text column and
** value for @DefinedLength are all 255. These need to all have
** the same values. 255 was selected in order for the max length
** display using down level clients
*/
,@SyscomText    nvarchar(4000)
,@Line          
nvarchar(255)

set @SearchTerm ‘MyTable’
set @dbname db_name()


set @SearchTerm ‘%’ + @SearchTerm + ‘%’

Select @DefinedLength 255
SELECT @BlankSpaceAdded /*Keeps track of blank spaces at end of lines. Note Len function ignores
                             trailing blank spaces*/
CREATE TABLE #CommentText
(obj_name 
varchar(255)
 ,LineId    
int
 
,Text  nvarchar(255collate database_default)

/*
**  Make sure the @objname is local to the current database.
*/
if @dbname is not null and @dbname <> db_name()
        
begin
                raiserror
(15250,-1,-1)
        
end

    begin

        DECLARE 
ms_crs_syscom  CURSOR LOCAL
        FOR SELECT 
so.name, sc.text FROM syscomments sc
    
inner join sysobjects so
    
on so.id = sc.id
    where 
so.type ‘P’
    
AND sc.encrypted 0
                
ORDER BY number, colid

        
FOR READ ONLY
    
end

/*
**  Get the text.
*/
SELECT @LFCR 2
SELECT @LineId 1


OPEN ms_crs_syscom

FETCH NEXT FROM ms_crs_syscom into @obj_name, @SyscomText

WHILE @@fetch_status >0
BEGIN

    SELECT  
@BasePos    1
    
SELECT  @CurrentPos 1
    
SELECT  @TextLength = LEN(@SyscomText)

    
IF @prev_obj_name <> @obj_name
        
–start again
        
SELECT @LineId 1
    
    
WHILE @CurrentPos  !0
    
BEGIN
        
–Looking for end of line followed by carriage return
        
SELECT @CurrentPos =   CHARINDEX(char(13)+char(10), @SyscomText, @BasePos)

        
–If carriage return found
        
IF @CurrentPos !0
        
BEGIN
            
/*If new value for @Lines length will be > then the
            **set length then insert current contents of @line
            **and proceed.
            */
            
While (isnull(LEN(@Line),0) + @BlankSpaceAdded + @CurrentPos-@BasePos + @LFCR) > @DefinedLength
            
BEGIN
                SELECT 
@AddOnLen @DefinedLength-(isnull(LEN(@Line),0) + @BlankSpaceAdded)
                
INSERT #CommentText VALUES
                
( @obj_name,
          @LineId,
                  
isnull(@Line, N”) + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N”))
                
SELECT @Line = NULL, @LineId @LineId + 1,
                       @BasePos 
@BasePos + @AddOnLen, @BlankSpaceAdded 0
            
END
            SELECT 
@Line    = isnull(@Line, N”) + isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos-@BasePos + @LFCR), N”)
            
SELECT @BasePos @CurrentPos+2
            
INSERT #CommentText VALUES( @obj_name, @LineId, @Line )
            
SELECT @LineId @LineId + 1
            
SELECT @Line = NULL
        END
        ELSE
        
–else carriage return not found
        
BEGIN
            IF 
@BasePos <@TextLength
            
BEGIN
                
/*If new value for @Lines length will be > then the
                **defined length
                */
                
While (isnull(LEN(@Line),0) + @BlankSpaceAdded + @TextLength-@BasePos+1 ) > @DefinedLength
                
BEGIN
                    SELECT 
@AddOnLen @DefinedLength – (isnull(LEN(@Line),0) + @BlankSpaceAdded)
                    
INSERT #CommentText VALUES
                    
( @obj_name,
              @LineId,
                      
isnull(@Line, N”) + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N”))
                    
SELECT @Line = NULL, @LineId @LineId + 1,
                        @BasePos 
@BasePos + @AddOnLen, @BlankSpaceAdded 0
                
END
                SELECT 
@Line = isnull(@Line, N”) + isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength-@BasePos+1 ), N”)
                
if LEN(@Line) < @DefinedLength and charindex(‘ ‘, @SyscomText, @TextLength+1 ) > 0
                
BEGIN
                    SELECT 
@Line @Line + ‘ ‘, @BlankSpaceAdded 1
                
END
            END
        END
    END
    
–Save previous name
    
SET @prev_obj_name @obj_name
    
FETCH NEXT FROM ms_crs_syscom into @obj_name, @SyscomText
END

IF 
@Line is NOT NULL
    INSERT #
CommentText VALUES( @obj_name, @LineId, @Line )

select from #CommentText 
where  patindex(@SearchTerm, text) <> 0

CLOSE  ms_crs_syscom
DEALLOCATE     ms_crs_syscom

DROP TABLE     #CommentText




Colorized by: CarlosAg.CodeColorizer