sp_msforeachdb 'use ?;select name,''?'' from sys.procedures where object_definition(object_id) like ''%text%'''

这将在所有数据库的所有存储过程中进行搜索。这也适用于较长的程序。

sp_msforeachdb 'use ?;select name,''?'' from sys.procedures where object_definition(object_id) like ''%text%'''

这将在所有数据库的所有存储过程中进行搜索。这也适用于较长的程序。

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

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

该查询返回在存储过程中引用“XXXX”的任何存储过程的名称和内容。

这在查找引用特定表/视图/过程的过程时非常有用

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

这是我在SQL 2012中编写的，以选择所有名称为“CompanySpecialisation”的存储过程。

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%'