如何找到包含＜text＞的存储过程？

梦里梦外; 2023-03-04 08:11 7阅读 0赞

本文翻译自：[How do I find a stored procedure containing ?][How do I find a stored procedure containing]

我需要在SQL Server 2008中搜索包含数据库字段名称或变量名称的存储过程。

--------------------

### \#1楼 ###

参考：[https://stackoom.com/question/ljoJ/如何找到包含-text-的存储过程][https_stackoom.com_question_ljoJ_-text-]

--------------------

### \#2楼 ###

*In case you needed schema as well:* **如果您还需要架构：**

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

--------------------

### \#3楼 ###

*You can also try [ApexSQL Search][] \- free SSMS plug-in from ApexSQL.* **您还可以尝试从ApexSQL免费获得[ApexSQL Search][] SSMS插件。**

![在此输入图像描述][4mDyt.jpg]

--------------------

### \#4楼 ###

SELECT s.name + '.' + o.name ProcedureName
    , c.text ProcedureSteps
    FROM   sys.syscomments c 
    INNER JOIN
    sys.objects o 
    ON 
    c.id = o.object_id
    INNER JOIN
    sys.schemas s 
    ON 
    o.schema_id = s.schema_id
    WHERE  o.type = 'P'
    AND c.text LIKE N'%XXXX%'
    ORDER BY s.name + '.' + o.name
    , c.colid

*This query returns the name and the content of any stored procedure where "XXXX" is is referenced within the stored procedure.* **此查询返回存储过程中引用“XXXX”的任何存储过程的名称和内容。**

*This is quit usefull when finding procedures that reference a specific table/view/procedure* **在查找引用特定表/视图/过程的过程时，这是有用的**

--------------------

### \#5楼 ###

select * from sys.system_objects
    where name like '%cdc%'

--------------------

### \#6楼 ###

SELECT * FROM sys.procedures WHERE Name LIKE '%CompanySpecialisation%'

这是我在SQL 2012中编写的选择所有存储过程的地方，例如“CompanySpecialisation”这样的名称

[How do I find a stored procedure containing]: https://oldbug.net/q/ljoJ/How-do-I-find-a-stored-procedure-containing-text
[https_stackoom.com_question_ljoJ_-text-]: https://stackoom.com/question/ljoJ/%E5%A6%82%E4%BD%95%E6%89%BE%E5%88%B0%E5%8C%85%E5%90%AB-text-%E7%9A%84%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B
[ApexSQL Search]: http://www.apexsql.com/sql_tools_search.aspx
[4mDyt.jpg]: https://i.stack.imgur.com/4mDyt.jpg