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

USE YOUR_DATABASE_NAME;

否则,系统。过程不会返回任何东西。现在运行查询如下所示:

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)上进行测试

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”的任何存储过程的名称和内容。

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

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

USE YOUR_DATABASE_NAME;

否则,系统。过程不会返回任何东西。现在运行查询如下所示:

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)上进行测试

如果你也需要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 

create Procedure [dbo].[TextFinder]
(@Text varchar(500),@Type varchar(2)=NULL) 
AS
BEGIN



SELECT DISTINCT o.name AS ObjectName,
CASE o.xtype

WHEN 'C' THEN 'CHECK constraint'

WHEN 'D' THEN 'Default or DEFAULT constraint'

WHEN 'F' THEN 'FOREIGN KEY constraint'

WHEN 'FN' THEN 'Scalar function'

WHEN 'IF' THEN 'In-lined table-function'

WHEN 'K' THEN 'PRIMARY KEY or UNIQUE constraint'

WHEN 'L' THEN 'Log'

WHEN 'P' THEN 'Stored procedure'

WHEN 'R' THEN 'Rule'

WHEN 'RF' THEN 'Replication filter stored procedure'

WHEN 'S' THEN 'System table'

WHEN 'TF' THEN 'Table function'

WHEN 'TR' THEN 'Trigger'`enter code here`

WHEN 'U' THEN 'User table'

WHEN 'V' THEN 'View'

WHEN 'X' THEN 'Extended stored procedure'

ELSE o.xtype

END AS ObjectType,

ISNULL( p.Name, '[db]') AS Location

FROM syscomments c

INNER JOIN sysobjects o ON c.id=o.id

LEFT JOIN sysobjects p ON o.Parent_obj=p.id

WHERE c.text LIKE '%' + @Text + '%' and

o.xtype = case when @Type IS NULL then o.xtype  else @Type end

ORDER BY Location, ObjectName

END

SELECT name FROM sys.procedures WHERE Object_definition(object_id) LIKE '%FOO%'