我接受了卡西夫的回答，把所有的答案结合在一起。奇怪的是，有时我在其中一个选择中找到了结果，而在另一个选择中却没有。为了安全起见，当我在寻找某个东西时，我会运行这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

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

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

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

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

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

SELECT OBJECT_NAME(object_id)
    FROM sys.sql_modules
    WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
    AND definition LIKE '%Foo%'

对于任何更新于SQL server 2000的SQL server:

SELECT object_name = OBJECT_NAME(sm.object_id), o.type_desc, sm.definition  
FROM sys.sql_modules AS sm  
JOIN sys.objects AS o ON sm.object_id = o.object_id  
WHERE sm.definition like '%searchString%'  
ORDER BY  o.type, o.name, o.object_id

如果有人被SQL server 2000卡住，表sql_modules不存在，所以你会使用syscomments，你会得到多个存储过程的记录大于4000个字符，但它们会有相同的c.number字段，所以你可以把这些部分组合在一起，以获得完整的存储过程文本:

    Select o.id, c.number, o.name, c.text  
    from syscomments c 
    inner join sysobjects o on o.id = c.id 
    where c.encrypted = 0 and o.type = 'P'  
      and c.id in  
     (Select id from syscomments where text like '%searchtext%')
    order by objecttype, o.name, o.id, c.number, c.colid        

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

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