试试下面的代码:

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]

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

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

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

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

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

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

我接受了卡西夫的回答，把所有的答案结合在一起。奇怪的是，有时我在其中一个选择中找到了结果，而在另一个选择中却没有。为了安全起见，当我在寻找某个东西时，我会运行这3个。希望这能有所帮助:

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

SELECT DISTINCT SPName 
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 T
ORDER BY T.SPName