试试下面的代码:

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.procedures WHERE Name LIKE '%CompanySpecialisation%'

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

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

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

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

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

下面是使用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