金牌萌帝-
问题描述
如何在所有存储过程中搜索一个模式，然后打开要编辑的存储过程?
How can you search all the Stored Procedures for a Pattern and then open the stored procedures to be edited?
SQL Server 2005 是否内置了任何东西?
Is there anything built inside of SQL Server 2005?
或者是否有任何第三方插件可以执行此搜索?
Or are there any 3rd party addins that will do this searching?
我也在使用 Red Gate 的 SQL Prompt，但我没有注意到那个选项.
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.
SQL Dependency Tracker 允许您动态探索所有数据库对象依赖项，使用一系列图形布局.这看起来在搜索模式时可以回答一些问题.还有其他类似 SQL Dependency Tracker 的软件吗?
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?
SQL 搜索 Redgate 是用于搜索的工具.它会在您键入时进行搜索(类似于 Bing 或 Google).它也很快！目前价格仍然免费(2/24/2011)，但我认为他们会在某个时候开始收费.
Edited: SQL Search by Redgate is the tool to use for Searching. It searches as you type (Similar to Bing or Google). It is also FAST! The price is still FREE right now (2/24/2011) but I think at some point they are going to start charging for it.
推荐答案
有一个名为 sp_grep 的开源存储过程，它允许您根据其 makup 的 DDL/代码查找数据库对象.我一直使用这个程序来查找满足特定标准的对象.这在数据库重构中非常有用.
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.
要以编程方式打开和修改 SQL 对象，您可以在任何 .Net 应用程序中使用 SQLDMO 对象.这里是一些使用 SQLDMO 的示例.
To programmatically open and modify SQL objects you can use the SQLDMO object in any .Net application. Here is some examples of using SQLDMO.
示例:exec sp_grep 'colA='
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: 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
