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

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

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

试试下面的代码:

DECLARE @SearchText NVARCHAR(2000) = N'YourText';
SELECT   DISTINCT SCHEMA_NAME(o.schema_id),o.name,[text] AS MainTextProc
FROM     sys.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    c.[text] LIKE '%'+ @SearchText +'%'
ORDER BY  SCHEMA_NAME(o.schema_id),o.[name]

自己抓一个免费的红门SQL搜索工具，开始享受在SQL Server搜索!：-)

这是一个非常有用的工具，是的!它是完全，绝对免费的任何用途。

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

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