如何查找包含<text>的存储过程？

Question

我需要在SQL Server 2008中搜索包含 WHERE 子句的存储过程，可能涉及数据库字段或变量名。

- Gary Kindel

Answer 1

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '%Foo%' 
    AND ROUTINE_TYPE='PROCEDURE'

---

SELECT OBJECT_NAME(id) 
    FROM SYSCOMMENTS 
    WHERE [text] LIKE '%Foo%' 
    AND OBJECTPROPERTY(id, 'IsProcedure') = 1 
    GROUP BY OBJECT_NAME(id)

---

SELECT OBJECT_NAME(object_id)
    FROM sys.sql_modules
    WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
    AND definition LIKE '%Foo%'

- Kashif

---

请注意，如果您的程序很长，routine_definition会被裁剪为4000个字符。请改用OBJECTPROPERTY方法。 - PeteT

完全同意@PeteT所说的话。几年前，这个4000字符限制真的让我很头疼。 - Vivian River

包含架构名称：OBJECT_SCHEMA_NAME(OBJECT_ID) AS [Schema]。 - Goldfish

你能否添加一些关于这三个查询分别是做什么的以及它们之间的区别的解释？ - Tot Zam

每个查询在做什么以及它们的差异或为什么一个更好的细节说明会很有帮助。这种类型的回答会鼓励经验较少的工程师盲目复制粘贴，并给那些使用新的关系型数据库管理系统（如SQL Server）的高级工程师带来沮丧。请给出详细说明，但不要改变原来的意思。 - DavidScherer

Answer 2

获取一份免费的Red-Gate SQL Search 工具，并开始愉快地在SQL Server中进行搜索！ :-)

这是一个非常棒和非常有用的工具，是的！它完全、绝对地免费使用。

- marc_s

---

这是一个非常好用的工具，我在回答这个问题之前已经在使用它了。值得注意的是，默认情况下，它将搜索结果限制为150条。查看此论坛帖子以获取有关如何增加该限制的信息。 - Scott

Answer 3

我采用了Kashif的答案，并将它们全部合并在一起。奇怪的是，有时候我会在一个选择器中找到结果，但在另一个选择器中找不到。因此，为了安全起见，在查找某些内容时，我会运行所有三个选择器。希望这有所帮助：

DECLARE @SearchText varchar(1000) = 'mytext';

SELECT DISTINCT SPName 
FROM (
    (SELECT ROUTINE_NAME SPName
        FROM INFORMATION_SCHEMA.ROUTINES 
        WHERE ROUTINE_DEFINITION LIKE '%' + @SearchText + '%' 
        AND ROUTINE_TYPE='PROCEDURE')
    UNION ALL
    (SELECT OBJECT_NAME(id) SPName
        FROM SYSCOMMENTS 
        WHERE [text] LIKE '%' + @SearchText + '%' 
        AND OBJECTPROPERTY(id, 'IsProcedure') = 1 
        GROUP BY OBJECT_NAME(id))
    UNION ALL
    (SELECT OBJECT_NAME(object_id) SPName
        FROM sys.sql_modules
        WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
        AND definition LIKE '%' + @SearchText + '%')
) AS T
ORDER BY T.SPName

- Losbear

Answer 4

你可以尝试使用来自ApexSQL的试用版本SSMS插件ApexSQL Search。

- David Smithers

Answer 5

如果您需要模式(schema)，这里也提供一下：

SELECT   DISTINCT SCHEMA_NAME(o.schema_id),o.name,[text]
FROM     syscomments AS c
         INNER JOIN sys.objects AS o ON c.id = o.[object_id]
         INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
WHERE    text LIKE '%foo%'
ORDER BY  SCHEMA_NAME(o.schema_id),o.name 

- Tom

Answer 6

首先确保你正在使用自己的用户凭据运行查询，并且在正确的数据库上下文中。

USE YOUR_DATABASE_NAME;

否则，sys.procedures 将不会返回任何内容。现在按照下面的查询运行：

select * from sys.procedures p 
join sys.syscomments s on p.object_id = s.id 
where text like '%YOUR_TEXT%';

另一种选项是使用INFORMATION_SCHEMA.ROUTINES.ROUTINE_DEFINITION，但请注意它仅保留例程的有限数量字符（即前4000个字符）。

select * from YOUR_DATABASE_NAME.INFORMATION_SCHEMA.ROUTINES
where ROUTINE_DEFINITION like '%YOUR_TEXT%';

我在 Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) 上进行了测试。

- Yuci

Answer 7

SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_DEFINITION LIKE '%FieldName%' 
AND ROUTINE_TYPE='PROCEDURE'

- Gary Kindel

---

但是要小心，因为这只是一个子字符串匹配。 "FieldName" 可能出现在注释中、字符串常量中或其他任何地方。此外，我怀疑寻找 [whatever] 不会捕捉到 whatever（当您正在寻找模式限定标识符时，这变得更加重要）。 - user

请参见上面被接受的答案中@PeteT的评论。INFORMATION_SCHEMA.ROUTINES.ROUTINE_DEFINITION被裁剪为4000个字符。因此，如果您有存储过程超过4000个字符，这种方法将无法正常工作。 - TMcManemy

Answer 8

我尝试了上面的例子，但它没有显示超过4000个字符，然后我进行了一些修改，就能够获取整个存储过程定义了。请参考下面更新的脚本 -

SELECT SCHEMA_NAME(O.SCHEMA_ID) [SCHEMA_NAME], O.NAME, OBJECT_DEFINITION(OBJECT_ID) TEXT
FROM   SYSCOMMENTS AS C
       INNER JOIN SYS.OBJECTS AS O ON C.ID = O.[OBJECT_ID]
       INNER JOIN SYS.SCHEMAS AS S ON O.SCHEMA_ID = S.SCHEMA_ID
WHERE  OBJECT_DEFINITION(OBJECT_ID) LIKE '%FOO%'
ORDER BY  SCHEMA_NAME(O.SCHEMA_ID), O.NAME

- Sandy

Answer 9

对于任何新于SQL Server 2000的SQL服务器：

SELECT object_name = OBJECT_NAME(sm.object_id), o.type_desc, sm.definition  
FROM sys.sql_modules AS sm  
JOIN sys.objects AS o ON sm.object_id = o.object_id  
WHERE sm.definition like '%searchString%'  
ORDER BY  o.type, o.name, o.object_id

如果有人使用 SQL Server 2000，而表 sql_modules 不存在，则需要使用 syscomments。对于大于 4000 个字符的存储过程，你将获得多个记录，但它们将具有相同的 c.number 字段，因此可以将它们组合在一起以获取完整的存储过程文本：

    Select o.id, c.number, o.name, c.text  
    from syscomments c 
    inner join sysobjects o on o.id = c.id 
    where c.encrypted = 0 and o.type = 'P'  
      and c.id in  
     (Select id from syscomments where text like '%searchtext%')
    order by objecttype, o.name, o.id, c.number, c.colid        

- Mourad

Answer 10

如何查找包含特定文本或字符串的存储过程

很多时候我们需要查找存储过程中的文本或字符串。以下是查找包含文本的查询语句。

SELECT OBJECT_NAME(id) 
FROM SYSCOMMENTS 
WHERE [text] LIKE '%Text%' 
AND OBJECTPROPERTY(id, 'IsProcedure') = 1 
GROUP BY OBJECT_NAME(id)

请查看下方给出的URL以获取更多信息。

http://www.freshcodehub.com/Article/34/how-to-find-a-stored-procedure-containing-text-or-string

- Kamal Pratap