SELECT DISTINCT OBJECT_NAME([id]),[text] 

 FROM syscomments   

 WHERE [id] IN (SELECT [id] FROM sysobjects WHERE xtype IN 

 ('TF','FN','V','P') AND status >= 0) AND  

 ([text] LIKE '%text to be search%' ) 

OBJECT_NAME([id])——>对象名称(视图，存储过程，标量函数，表函数名)

id (int) =对象标识号

xtype char(2)对象类型。可以是以下对象类型之一:

FN =标量函数

P =存储过程

V =视图

表函数

/* 
    SEARCH SPROCS & VIEWS

    The following query will allow search within the definitions 
    of stored procedures and views.

    It spits out the results as XML, with the full definitions, 
    so you can browse them without having to script them individually.

*/

/*
   STEP 1: POPULATE SEARCH KEYS. (Set to NULL to ignore)
*/
DECLARE 
    @def_key varchar(128) = '%foo%',      /* <<< definition search key */
    @name_key varchar(128) = '%bar%',     /* <<< name search key       */
    @schema_key varchar(128) = 'dbo';     /* <<< schema search key     */

;WITH SearchResults AS (
    /* 
       STEP 2: DEFINE SEARCH QUERY AS CTE (Common Table Expression)
    */
    SELECT 
        [Object].object_id                       AS [object_id],    
        [Schema].name                            AS [schema_name], 
        [Object].name                            AS [object_name],
        [Object].type                            AS [object_type],
        [Object].type_desc                       AS [object_type_desc],
        [Details].definition                     AS [module_definition]
    FROM  
        /* sys.sql_modules = where the body of sprocs and views live */
        sys.sql_modules AS [Details] WITH (NOLOCK)
    JOIN
        /* sys.objects = where the metadata for every object in the database lives */
        sys.objects AS [Object] WITH (NOLOCK) ON [Details].object_id = [Object].object_id
    JOIN 
        /* sys.schemas = where the schemas in the datatabase live */
        sys.schemas AS [Schema] WITH (NOLOCK) ON [Object].schema_id = [Schema].schema_id
    WHERE 
        (@def_key IS NULL OR [Details].definition LIKE @def_key)      /* <<< searches definition */
        AND (@name_key IS NULL OR [Object].name LIKE @name_key)       /* <<< searches name       */
        AND (@schema_key IS NULL OR [Schema].name LIKE @schema_key)   /* <<< searches schema     */
)
/* 
   STEP 3: SELECT FROM CTE INTO XML
*/

/* 
    This outer select wraps the inner queries in to the <sql_object> root element 
*/
SELECT 
(
    /* 
        This inner query maps stored procedure rows to <procedure> elements
    */
    SELECT TOP 100 PERCENT
        [object_id]                            AS [@object_id], 
        [schema_name] + '.' + [object_name]    AS [@full_name],
        [module_definition]                    AS [module_definition]
    FROM
        SearchResults
    WHERE
        object_type = 'P'
    ORDER BY
        [schema_name], [object_name]
    FOR XML
        PATH ('procedure'), TYPE
) AS [procedures],  /* <<< as part of the outer query, 
                           this alias causes the <procedure> elements
                           to be wrapped within the <procedures> element */
(
    /* 
        This inner query maps view rows to <view> elements
    */
    SELECT TOP 100 PERCENT 
        [object_id]                            AS [@object_id], 
        [schema_name] + '.' + [object_name]    AS [@full_name],
        [module_definition]                    AS [module_definition]
    FROM
        SearchResults
    WHERE
        object_type = 'V'
    ORDER BY
        [schema_name], [object_name]
    FOR XML
        PATH ('view'), TYPE
) AS [views]  /* <<< as part of the outer query, 
                     this alias causes the <view> elements
                     to be wrapped within the <views> element */
FOR XML 
    PATH ('sql_objects')

Select distinct OBJECT_NAME(id) from syscomments where text like '%string%' AND OBJECTPROPERTY(id, 'IsProcedure') = 1 

你有没有试过使用一些第三方工具来进行搜索?有几个可用的免费软件，在过去为我节省了大量的时间。

下面是我成功使用的两个SSMS插件。

ApexSQL搜索-搜索数据库中的模式和数据，并具有额外的功能，如依赖跟踪和更多…

SSMS工具包-具有与前一个相同的搜索功能和其他一些很酷的功能。不是免费的SQL Server 2012，但仍然非常实惠。

我知道这个答案不是100%与问题相关(更具体)，但希望其他人会发现这个有用。

试试这个请求:

查询

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

这个搜索例程基于https://stackoverflow.com/a/33631029/2735286，并且在搜索结果中也包含了模式名:

CREATE PROCEDURE [dbo].[Searchinall] (@strFind AS VARCHAR(MAX))
AS
BEGIN
    SET NOCOUNT ON; 
    --TO FIND STRING IN ALL PROCEDURES        
    BEGIN
        SELECT s.name SP_Schema_Name, OBJECT_NAME(p.OBJECT_ID) SP_Name
              ,OBJECT_DEFINITION(p.OBJECT_ID) SP_Definition
            FROM   sys.procedures p
            INNER JOIN sys.schemas s on p.schema_id = s.schema_id
            WHERE  OBJECT_DEFINITION(OBJECT_ID) LIKE '%'+@strFind+'%'
    END 

    --TO FIND STRING IN ALL VIEWS        
    BEGIN
        SELECT s.name SP_Schema_Name, OBJECT_NAME(OBJECT_ID) View_Name
                ,OBJECT_DEFINITION(OBJECT_ID) View_Definition
            FROM   sys.views v
            INNER JOIN sys.schemas s on v.schema_id = s.schema_id
            WHERE  OBJECT_DEFINITION(OBJECT_ID) LIKE '%'+@strFind+'%'
    END 

    --TO FIND STRING IN ALL FUNCTION        
    BEGIN
        SELECT ROUTINE_SCHEMA, ROUTINE_NAME           Function_Name
              ,ROUTINE_DEFINITION     Function_definition
        FROM   INFORMATION_SCHEMA.ROUTINES
        WHERE  ROUTINE_DEFINITION LIKE '%'+@strFind+'%'
               AND ROUTINE_TYPE = 'FUNCTION'
        ORDER BY
               ROUTINE_NAME
    END

    --TO FIND STRING IN ALL TABLES OF DATABASE.    
    BEGIN

        SELECT s.name SP_Schema_Name, t.name      AS Table_Name
                ,c.name      AS COLUMN_NAME
        FROM   sys.tables  AS t
                INNER JOIN sys.columns c
                    ON  t.OBJECT_ID = c.OBJECT_ID
                INNER JOIN sys.schemas s on t.schema_id = s.schema_id
        WHERE  c.name LIKE '%'+@strFind+'%'
        ORDER BY
                Table_Name
    END
END

下面是如何使用它:

execute [dbo].[Searchinall] 'cust'

下面是一个替代方法，它在一个查询中列出了所有具有特定关键字的对象:

SELECT DISTINCT
       s.name AS Schema_Name, 
       o.name AS Object_Name,
       o.type_desc
FROM sys.sql_modules m
       INNER JOIN
       sys.objects o
         ON m.object_id = o.object_id
       INNER JOIN sys.schemas s on o.schema_id = s.schema_id
WHERE m.definition Like '%dim_forex%' ESCAPE '\' order by 3;

此查询应返回与应答中的过程调用相同的结果。