SQL Server 2005中的存储过程中的文本搜索

How do I find a stored procedure containing a certain text? While I understand that the best place to do this kind of searching is through your source control tool, but are there ways to do this in the database?

如何查找包含特定文本的存储过程?虽然我知道进行这种搜索的最佳位置是通过源代码控制工具,但有没有办法在数据库中执行此操作?

3 个解决方案

#1

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

#2

SELECT DISTINCT o.name AS Object_Name,o.type_desc
FROM sys.sql_modules        m 
INNER JOIN sys.objects  o 
    ON m.object_id=o.object_id
WHERE m.definition Like '%Serach_Text%'

#3

You can search sys.sql_modules. Definition contains the text of procedures. The view contains procedures, views, udfs etc. To restrict yourself to stored procedures you should join with sys.procedure on object_id.

您可以搜索sys.sql_modules。定义包含程序文本。该视图包含过程,视图,udf等。要限制自己使用存储过程,您应该在object_id上使用sys.procedure加入。