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

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

下面是使用Losbear制作的联合版本，但修改后它也输出模式名:

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

SELECT DISTINCT sc.SPECIFIC_SCHEMA as 'Schema', sp.SPName as 'Name'
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 sp
CROSS APPLY (select SPECIFIC_SCHEMA from INFORMATION_SCHEMA.ROUTINES r where r.ROUTINE_NAME = sp.SPName) sc
ORDER BY 1

用于在SP中查找文本的存储过程。{迪尼希·巴斯卡兰}新潮全球系统私人有限公司

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

我尝试了上面的例子，但它没有显示超过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

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