Ways to Search for a Pattern in all Stored Procedures and then Open it to be Altered

Question

How can you search all the Stored Procedures for a Pattern and then open the stored procedures to be edited?

Is there anything built inside of SQL Server 2005?

Or are there any 3rd party addins that will do this searching?

I am also using Red Gate's SQL Prompt but I have not noticed that option.

Currently I am using the following command to do the searching

SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_DEFINITION 
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '%tblVacationAllocationItem%' 
    AND ROUTINE_TYPE='PROCEDURE'
    ORDER BY ROUTINE_SCHEMA, ROUTINE_NAME

This works pretty good but it returns the content of the stored procedure in one of the columns, which is hard to read. So I have to use the Object Explorer to find and open the stored procedure to see the full content.

Edited: SQL Dependency Tracker allows you to dynamically explore all your database object dependencies, using a range of graphical layouts. This looks like it would answer a few of the questions when searching for a pattern. Any other software similar to SQL Dependency Tracker?

Answer 1

Unfortunately, there is no feature in SQL Server 2005 other than querying, to find stored procedures based on their contents. The only thing you can do is filter by name in the object explorer.

I use Visual Studio Database Edition to accomplish this task.

Answer 2

There is a open source stored procedure called sp_grep that allows for you to find database objects based on the DDL/code of their makup. I use this procedure all the time to find objects that meet certain criteria. This is very useful in Database refactoring.

To programmatically open and modify SQL objects you can use the SQLDMO object in any .Net application. Here is some examples of using SQLDMO.

Example: exec sp_grep 'colA='

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

/*********************************************************************
* Stored procedure  sp_grep 
* SQL Server:   Microsoft SQL Server 6.0, 4.21 for Windows NT, 
*               Microsoft SQL Server 4.2 for OS/2.
* Author:       Andrew Zanevsky, AZ Databases, Inc.
* Version/Date: Version 1.1,  October 26, 1995
* Description:  Searches syscomments table in the current database
*               for occurences of a combination of strings. 
*               Correclty handles cases when a substring begins in 
*               one row of syscomments and continues in the next. 
* Parameters: - @parameter describes the search:
*               string1 {operation1 string2} {operation2 string 3} ...
*               where - stringN is a string of characters enclosed in
*                       curly brackets not longer than 80 characters. 
*                       Brackets may be omitted if stringN does not 
*                       contain spaces or characters: +,-,&;
*                     - operationN is one of the characters: +,-,&.
*               Parameter is interpreted as follows:
*               1.Compose the list of all objects where string1 occurs.
*               2.If there is no more operations in the parameter,
*                 then display the list and stop. Otherwise continue.
*               3.If the next operation is + then add to the list all 
*                   objects where the next string occurs;
*                 else if the next operation is - then delete from the 
*                   list all objects where the next string occurs;
*                 else if the next operation is & then delete from the 
*                   list all objects where the next string does not 
*                   occur (leave in the list only those objects where 
*                   the next string occurs);
*               4.Goto step 2.
*               Parameter may be up to 255 characters long, and may not 
*               contain <CarriageReturn> or <LineFeed> characters.
*               Please note that operations are applied in the order
*               they are used in the parameter string (left to right). 
*               There is no other priority of executing them. Every 
*               operation is applied to the list combined as a result 
*               of all previous operations.
*               Number of spaces between words of a string matters in a
*               search (e.g. "select *" is not equal to "select  *").
*               Short or frequently used strings (such as "select") may 
*               produce a long result set.
*
*             - @case: i = insensitive / s = sensitive (default)
*               Insensitive search is performed regardless of this parameter 
*               if SQL Server is set up with case insensitive sort order.
*
* Examples:     sp_grep employee 
*                 list all objects where string 'employee' occurs;
*               sp_grep employee, i
*                 list all objects where string 'employee' occurs in 
*                 any case (upper, lower, or mixed), such as 
*                 'EMPLOYEE', 'Employee', 'employee', etc.;
*               sp_grep 'employee&salary+department-trigger'
*                 list all objects where either both strings 'employee'
*                 and 'salary' occur or string 'department' occurs, and 
*                 string 'trigger' does not occur;
*               sp_grep '{select FirstName + LastName}'
*                 list all objects where string 
*                 "select FirstName + LastName" occurs;
*               sp_grep '{create table}-{drop table}'
*                 list all objects where tables are created and not 
*                 dropped.
*                 
**********************************************************************/

-- sp_grep   v1.0 03/16/1995, v1.1 10/26/1995
-- Author:   Andrew Zanevsky, AZ Databases, Inc. 
-- E-mail:   [email protected]
ALTER proc [dbo].[sp_grep] @parameter varchar(255) = null, @case char(1) = 's'
as

declare @str_no          tinyint, 
        @msg_str_no      varchar(3),
        @operation       char(1), 
        @string          varchar(80), 
        @oper_pos        smallint,
        @context         varchar(255),
        @i               tinyint,
        @longest         tinyint,
        @msg             varchar(255)

if @parameter is null /* provide instructions */
begin
    print 'Execute sp_grep "{string1}operation1{string2}operation2{string3}...", [case]'
    print '- stringN is a string of characters up to 80 characters long, '
    print '  enclosed in curly brackets. Brackets may be omitted if stringN '
    print '  does not contain leading and trailing spaces or characters: +,-,&.'
    print '- operationN is one of the characters: +,-,&. Interpreted as or,minus,and.'
    print '  Operations are executed from left to right with no priorities.'
    print '- case: specify "i" for case insensitive comparison.'
    print 'E.g. sp_grep "alpha+{beta gamma}-{delta}&{+++}"'
    print '     will search for all objects that have an occurence of string "alpha"'
    print '     or string "beta gamma", do not have string "delta", '
    print '     and have string "+++".'
    return
end

/* Check for <CarriageReturn> or <LineFeed> characters */
if charindex( char(10), @parameter ) > 0 or charindex( char(13), @parameter ) > 0
begin
    print 'Parameter string may not contain <CarriageReturn> or <LineFeed> characters.'
    return
end

if lower( @case ) = 'i'
        select  @parameter = lower( ltrim( rtrim( @parameter ) ) )
else
        select  @parameter = ltrim( rtrim( @parameter ) )

create table #search ( str_no tinyint, operation char(1), string varchar(80), last_obj int )
create table #found_objects ( id int, str_no tinyint )
create table #result ( id int )

/* Parse the parameter string */
select @str_no = 0
while datalength( @parameter ) > 0
begin
  /* Get operation */
  select @str_no = @str_no + 1, @msg_str_no = rtrim( convert( char(3), @str_no + 1 ) )
  if @str_no = 1
    select  @operation = '+'
  else 
  begin
    if substring( @parameter, 1, 1 ) in ( '+', '-', '&' )
        select  @operation = substring( @parameter, 1, 1 ),
                @parameter = ltrim( right( @parameter, datalength( @parameter ) - 1 ) )
    else
    begin
        select @context = rtrim( substring( 
                        @parameter + space( 255 - datalength( @parameter) ), 1, 20 ) )
        select @msg = 'Incorrect or missing operation sign before "' + @context + '".'
        print  @msg 
        select @msg = 'Search string ' + @msg_str_no + '.'
        print  @msg 
        return
    end
  end

  /* Get string */
  if datalength( @parameter ) = 0
  begin
      print 'Missing search string at the end of the parameter.'
      select @msg = 'Search string ' + @msg_str_no + '.'
      print  @msg 
      return
  end
  if substring( @parameter, 1, 1 ) = '{'
  begin
      if charindex( '}', @parameter ) = 0
      begin
          select @context = rtrim( substring( 
                      @parameter + space( 255 - datalength( @parameter) ), 1, 200 ) )
          select @msg = 'Bracket not closed after "' + @context + '".'
          print  @msg 
          select @msg = 'Search string ' + @msg_str_no + '.'
          print  @msg 
          return
      end
      if charindex( '}', @parameter ) > 82
      begin
          select @context = rtrim( substring( 
                      @parameter + space( 255 - datalength( @parameter) ), 2, 20 ) )
          select @msg = 'Search string ' + @msg_str_no + ' is longer than 80 characters.'
          print  @msg 
          select @msg = 'String begins with "' + @context + '".'
          print  @msg 
          return
      end        
      select  @string    = substring( @parameter, 2, charindex( '}', @parameter ) - 2 ),
              @parameter = ltrim( right( @parameter, 
                              datalength( @parameter ) - charindex( '}', @parameter ) ) )
  end
  else
  begin
      /* Find the first operation sign */
      select @oper_pos = datalength( @parameter ) + 1
      if charindex( '+', @parameter ) between 1 and @oper_pos
          select @oper_pos = charindex( '+', @parameter )
      if charindex( '-', @parameter ) between 1 and @oper_pos
          select @oper_pos = charindex( '-', @parameter )
      if charindex( '&', @parameter ) between 1 and @oper_pos
          select @oper_pos = charindex( '&', @parameter )

      if @oper_pos = 1
      begin
          select @context = rtrim( substring( 
                      @parameter + space( 255 - datalength( @parameter) ), 1, 20 ) )
          select @msg = 'Search string ' + @msg_str_no + 
                        ' is missing, before "' + @context + '".'
          print  @msg 
          return
      end        
      if @oper_pos > 81
      begin
          select @context = rtrim( substring( 
                      @parameter + space( 255 - datalength( @parameter) ), 1, 20 ) )
          select @msg = 'Search string ' + @msg_str_no + ' is longer than 80 characters.'
          print  @msg 
          select @msg = 'String begins with "' + @context + '".'
          print  @msg 
          return
      end        

      select  @string    = substring( @parameter, 1, @oper_pos - 1 ),
              @parameter = ltrim( right( @parameter, 
                              datalength( @parameter ) - @oper_pos + 1 ) )
  end
  insert #search values ( @str_no, @operation, @string, 0 )

end
select @longest = max( datalength( string ) ) - 1
from   #search
/* ------------------------------------------------------------------ */
/* Search for strings */
if @case = 'i'
begin
    insert #found_objects
    select a.id, c.str_no
    from   syscomments a, #search c
    where  charindex( c.string, lower( a.text ) ) > 0

    insert #found_objects
    select a.id, c.str_no
    from   syscomments a, syscomments b, #search c
    where  a.id        = b.id
    and    a.number    = b.number
    and    a.colid + 1 = b.colid
    and    charindex( c.string, 
                lower( right( a.text, @longest ) + 
/*                     space( 255 - datalength( a.text ) ) +*/
                       substring( b.text, 1, @longest ) ) ) > 0
end
else
begin
    insert #found_objects
    select a.id, c.str_no
    from   syscomments a, #search c
    where  charindex( c.string, a.text ) > 0

    insert #found_objects
    select a.id, c.str_no
    from   syscomments a, syscomments b, #search c
    where  a.id        = b.id
    and    a.number    = b.number
    and    a.colid + 1 = b.colid
    and    charindex( c.string, 
                right( a.text, @longest ) + 
/*              space( 255 - datalength( a.text ) ) +*/
                substring( b.text, 1, @longest ) ) > 0
end
/* ------------------------------------------------------------------ */
select distinct str_no, id into #dist_objects from #found_objects
create unique clustered index obj on #dist_objects  ( str_no, id )

/* Apply one operation at a time */
select @i = 0
while @i < @str_no
begin
    select @i = @i + 1
    select @operation = operation from #search where str_no = @i

    if @operation = '+'
        insert #result
        select id
        from   #dist_objects 
        where  str_no = @i
    else if @operation = '-'
        delete #result
        from   #result a, #dist_objects b
        where  b.str_no = @i
        and    a.id = b.id
    else if @operation = '&'
        delete #result
        where  not exists 
                ( select 1
                  from   #dist_objects b
                  where  b.str_no = @i
                  and    b.id = #result.id )
end

/* Select results */
select distinct id into #dist_result from #result

/* The following select has been borrowed from the sp_help 
** system stored procedure, and modified. */
select  Name        = o.name,
        /* Remove 'convert(char(15)' in the following line 
        ** if user names on your server are longer. */
        Owner       = convert( char(15), user_name(uid) ),
        Object_type = substring(v.name + x.name, 1, 16)
from    #dist_result           d,
        sysobjects             o, 
        master.dbo.spt_values  v,
        master.dbo.spt_values  x
where   d.id = o.id
/* SQL Server version 6.x uses 15, prior versions use 7 in expression below */
and     o.sysstat & ( 7 + 8 * sign( charindex( '6.', @@version ) ) ) = v.number
and     v.type = "O"
and     x.type = "R"
and     o.userstat & -32768 = x.number
order by Object_type desc, Name asc

Answer 3

To query the definition of an object, one could use syscomments. For example:

select * from syscomments where text like '%tblVacationAllocationItem%'

While this will work for most scenarios, if the definition is longer than 4000 characters, there will exist multiple syscomment rows for a single object. Although unlikely, it is possible that your search phrase spans multiple syscomment rows.

Answer 4

Not the answer to your question, but we save all our SProcs as separate files - easier to globally make changes using a Programmer's Editor, and they are easy to get into version management repository (SVN in our case)

Answer 5

I have posted the code in the following article for a Stored Proc that works in SQL 2000 and above that does a comprehensive search (across Procs, Functions, Views, Defaults, Jobs, etc.) and can optionally ignore comments and optionally ignore string literals. You can find it at SQLServerCentral.com :

http://www.sqlservercentral.com/articles/Stored+Procedure/62975/

It does not automatically open anything for editing but does give the line number of where it matches the text and takes into account the 4000-character chunks as mentioned by Cadaeic. I was planning on updating the article soon to include a loop across all databases and even to include some of my SQL# RegEx functions (for Object Name matching as well as the search string).

Answer 6

begin
--select column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='Products' 
--Declare the Table variable 
DECLARE @GeneratedStoredProcedures TABLE
(
  Number INT IDENTITY(1,1), --Auto incrementing Identity column
  name VARCHAR(300) --The string value
)

--Decalre a variable to remember the position of the current delimiter
DECLARE @CurrentDelimiterPositionVar INT 
declare @sqlCode varchar(max)
--Decalre a variable to remember the number of rows in the table
DECLARE @Count INT

--Populate the TABLE variable using some logic
INSERT INTO @GeneratedStoredProcedures SELECT name FROM sys.procedures where name like 'procGen_%'

--Initialize the looper variable
SET @CurrentDelimiterPositionVar = 1

--Determine the number of rows in the Table
SELECT @Count=max(Number) from @GeneratedStoredProcedures

--A variable to hold the currently selected value from the table
DECLARE @CurrentValue varchar(300);

--Loop through until all row processing is done
WHILE @CurrentDelimiterPositionVar <= @Count
BEGIN
 --Load current value from the Table
 SELECT @CurrentValue = name FROM @GeneratedStoredProcedures WHERE Number = @CurrentDelimiterPositionVar 
 --Process the current value
 --print @CurrentValue
 set @sqlCode = 'drop procedure ' + @CurrentValue
 print @sqlCode
 --exec (@sqlCode)


 --Increment loop counter
 SET @CurrentDelimiterPositionVar = @CurrentDelimiterPositionVar + 1;
END

end