试试下面的代码:

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]

如果你也需要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 * from sys.system_objects
where name like '%cdc%'

如何找到一个包含文本或字符串的存储过程

很多时候，我们需要在存储过程中查找文本或字符串。下面是查找包含文本的查询。

SELECT OBJECT_NAME(id) 
FROM SYSCOMMENTS 
WHERE [text] LIKE '%Text%' 
AND OBJECTPROPERTY(id, 'IsProcedure') = 1 
GROUP BY OBJECT_NAME(id)

欲了解更多信息，请查看下面给出的URL。

http://www.freshcodehub.com/Article/34/how-to-find-a-stored-procedure-containing-text-or-string

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

试试下面的代码:

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]