sql - 在所有存储过程中搜索模式然后打开它进行更改的方法

Question

如何在所有存储过程中搜索某个模式，然后打开要编辑的存储过程？

SQL Server 2005 内部有内置的东西吗？

或者是否有任何第三方插件可以进行此搜索？

我也在使用 Red Gate 的 SQL Prompt，但我没有注意到该选项。

目前我正在使用以下命令进行搜索

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

这工作得很好，但它在其中一列中返回存储过程的内容，这很难阅读。所以我必须使用对象资源管理器来查找并打开存储过程才能看到完整的内容。

编辑: SQL Dependency Tracker允许您使用一系列图形布局动态探索所有数据库对象依赖关系。这看起来可以回答搜索模式时的一些问题。还有其他类似于 SQL Dependency Tracker 的软件吗？

编辑: SQL Search通过 Redgate是用于搜索的工具。它会在您键入时进行搜索(类似于 Bing 或 Google)。它也很快!现在(2/24/2011)价格仍然免费，但我认为在某个时候他们将开始收费。

Answer 1

有一个名为 sp_grep 的开源存储过程，它允许您根据数据库对象的 DDL/代码查找数据库对象。我一直使用这个过程来查找满足特定条件的对象。这在数据库重构中非常有用。

要以编程方式打开和修改 SQL 对象，您可以在任何 .Net 应用程序中使用 SQLDMO 对象。 Here是使用 SQLDMO 的一些示例。

示例: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:   zanevsky@azdatabases.com
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