您可以搜索 SQL Server 2005 存储过程内容吗? [英] Can you search SQL Server 2005 Stored Procedure content?

问题描述

SQL Server Server 2005.我盯着一个拥有 500 多个存储过程的数据库，并试图收集它们如何与数据交互的复杂性 - 特别是在它们如何插入/修改数据方面.我希望找到一个搜索"或查找"功能来查看实际过程的内容.这样，我可以搜索所有使用 some_table_name 执行任何操作的过程.SQL 管理的基本查找功能Studio 在打开的文件中查找，如果我已经打开了 sproc，则在文件中查找只会查找内容，即使这样也只能在 ...\Local Settings\Temp\~vs1011.sql 临时类型文件中查找.

SQL Server Server 2005. I'm staring at a database that has 500+ stored procedures, and trying to glean the intricacies of how they interact with the data - particularly in regards to how they insert/modify data. I was hoping to find a search" or "find" functionality that would look at the content of the actual procedure. That way, I could do a search for all procedures that do anything at all with some_table_name. The basic find functionality of SQL Management Studio looks in opened files, and Find in Files only appears to find the content if I already have the sproc opened, and even then only in the ...\Local Settings\Temp\~vs1011.sql temporary type files.

截至目前，我知道进入底层过程的唯一方法是右键单击并选择修改"(或脚本存储过程为 => 创建或更改).是否有更快/更简单的方法来搜索/检查所有 sproc?

As of right now, the only way I know of getting to the underlying procedure is to right click and select "modify" (or Script Stored Procedure As => Create or Alter). Is there a faster/easier way to search/examine all the sprocs?

推荐答案

您可以使用一个 Information_Schema.Routines 视图.

There is an Information_Schema.Routines view that you can use.

select * 
FROM   INFORMATION_SCHEMA.ROUTINES 
WHERE  OBJECTPROPERTY(OBJECT_ID(SPECIFIC_NAME),'IsMSShipped') =0 
       and OBJECT_DEFINITION(OBJECT_ID(SPECIFIC_NAME)) like '%search term here%' 
       AND ROUTINE_TYPE='PROCEDURE'

这篇关于您可以搜索 SQL Server 2005 存储过程内容吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！