如何grep SQL Server存储过程？

I would like to run a standard grep over all stored procedures in a given SQL Server database (assume 2005 or later). I have found a variety of simple queries to list the names of stored procedures containing a specific object, e.g.

我想对给定SQL Server数据库中的所有存储过程运行标准grep（假设2005或更高版本）。我找到了各种简单的查询来列出包含特定对象的存储过程的名称，例如

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%table_I_want_to_find%'

But what I really want is, like grep, to list the specific lines in the identified stored procedures (so I do not have to manually open each one and see if it is what I am looking for).

但我真正想要的是，像grep一样，列出已识别的存储过程中的特定行（因此我不必手动打开每一行，看看它是否是我正在寻找的）。

I am open to solutions in T-SQL or PowerShell, or even an off-the-shelf utility.

我对T-SQL或PowerShell中的解决方案持开放态度，甚至是现成的实用程序。

3 个解决方案

#1

Use SQL Search from Red Gate. It's a free tool and is fantastic.

使用Red Gate中的SQL搜索。这是一个免费的工具，太棒了。

#2

I am brand new to SQL Server, but the following seems to work great and has proved itself to be quite useful already. I am using SQL Server 2008.

我是SQL Server的新手，但以下似乎工作得很好，已证明自己已经非常有用。我正在使用SQL Server 2008。

select ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_BODY, ROUTINE_DEFINITION
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_DEFINITION like '%searchtext%'
order by ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION;

For completeness, here are some similar queries you'll probably want to know about:

为了完整起见，以下是您可能想要了解的一些类似查询：

exec sp_help 'myTable'

select name
from sys.all_views
order by name;

select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
from INFORMATION_SCHEMA.TABLES
order by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE;

select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE
from INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME = 'myColumn'
order by TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME;

#3

You could use SMO to dump the stored procedure DDL to files, and then use grep on them.

您可以使用SMO将存储过程DDL转储到文件，然后在它们上使用grep。

There are some examples of extracting the DDL here (called Scripting).

这里有一些提取DDL的例子（称为Scripting）。

#1