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 =视图

表函数

此查询是从所有数据库中搜索存储过程中的文本。

DECLARE @T_Find_Text VARCHAR(1000) = 'Foo'

IF OBJECT_ID('tempdb..#T_DBNAME') IS NOT NULL DROP TABLE #T_DBNAME
IF OBJECT_ID('tempdb..#T_PROCEDURE') IS NOT NULL DROP TABLE #T_PROCEDURE

CREATE TABLE #T_DBNAME
(
    IDX int IDENTITY(1,1) PRIMARY KEY 
    , DBName VARCHAR(255)
)

CREATE TABLE #T_PROCEDURE
(
    IDX int IDENTITY(1,1) PRIMARY KEY 
    , DBName VARCHAR(255)
    , Procedure_Name VARCHAR(MAX)
    , Procedure_Description VARCHAR(MAX)
)

INSERT INTO #T_DBNAME (DBName)
SELECT name FROM master.dbo.sysdatabases

DECLARE @T_C_IDX INT = 0
DECLARE @T_C_DBName VARCHAR(255)
DECLARE @T_SQL NVARCHAR(MAX)
DECLARE @T_SQL_PARAM NVARCHAR(MAX) 

SET @T_SQL_PARAM =   
    '   @T_C_DBName VARCHAR(255)
        , @T_Find_Text VARCHAR(255)
    '  


WHILE EXISTS(SELECT TOP 1 IDX FROM #T_DBNAME WHERE IDX > @T_C_IDX ORDER BY IDX ASC)
BEGIN

    SELECT TOP 1 
    @T_C_DBName = DBName 
    FROM #T_DBNAME WHERE IDX > @T_C_IDX ORDER BY IDX ASC

    SET @T_SQL = ''

    SET @T_SQL = @T_SQL + 'INSERT INTO #T_PROCEDURE(DBName, Procedure_Name, Procedure_Description)'
    SET @T_SQL = @T_SQL + 'SELECT SPECIFIC_CATALOG, ROUTINE_NAME, ROUTINE_DEFINITION '
    SET @T_SQL = @T_SQL + 'FROM ' + @T_C_DBName +  '.INFORMATION_SCHEMA.ROUTINES  '
    SET @T_SQL = @T_SQL + 'WHERE ROUTINE_DEFINITION LIKE ''%''+ @T_Find_Text + ''%'' '
    SET @T_SQL = @T_SQL + 'AND ROUTINE_TYPE = ''PROCEDURE'' '

    BEGIN TRY
        EXEC SP_EXECUTESQL  @T_SQL, @T_SQL_PARAM, @T_C_DBName, @T_Find_Text
    END TRY
    BEGIN CATCH
        SELECT @T_C_DBName + ' ERROR'
    END CATCH

    SET @T_C_IDX = @T_C_IDX + 1
END

SELECT IDX, DBName, Procedure_Name FROM #T_PROCEDURE ORDER BY DBName ASC

我创建了一个过程来搜索过程/函数、表、视图或作业中的文本。第一个参数@search是搜索条件，@target是搜索目标，即过程、表等。如果未指定，则搜索全部。@db是指定要搜索的数据库，默认为当前数据库。下面是我在动态SQL中的查询。

ALTER PROCEDURE [dbo].[usp_find_objects]
(
    @search VARCHAR(255),
    @target VARCHAR(255) = NULL,
    @db VARCHAR(35) = NULL
)
AS

SET NOCOUNT ON;

DECLARE @TSQL NVARCHAR(MAX), @USEDB NVARCHAR(50)

IF @db <> '' SET @USEDB = 'USE ' + @db
ELSE SET @USEDB = ''

IF @target IS NULL SET @target = ''

SET @TSQL = @USEDB + '

DECLARE @search VARCHAR(128) 
DECLARE @target VARCHAR(128)

SET @search = ''%' + @search + '%''
SET @target = ''' + @target + '''

IF @target LIKE ''%Procedure%'' BEGIN
    SELECT o.name As ''Stored Procedures''
    FROM SYSOBJECTS o 
    INNER JOIN SYSCOMMENTS c ON o.id = c.id
    WHERE c.text LIKE @search
        AND o.xtype IN (''P'',''FN'')
    GROUP BY o.name
    ORDER BY o.name
END

ELSE IF @target LIKE ''%View%'' BEGIN
    SELECT o.name As ''Views''
    FROM SYSOBJECTS o 
    INNER JOIN SYSCOMMENTS c ON o.id = c.id
    WHERE c.text LIKE @search
        AND o.xtype = ''V''
    GROUP BY o.name
    ORDER BY o.name
END

/* Table - search table name only, need to add column name */
ELSE IF @target LIKE ''%Table%'' BEGIN
    SELECT t.name AS ''TableName''
    FROM sys.columns c 
    JOIN sys.tables t ON c.object_id = t.object_id
    WHERE c.name LIKE @search
    ORDER BY TableName
END

ELSE IF @target LIKE ''%Job%'' BEGIN
    SELECT  j.job_id,
        s.srvname,
        j.name,
        js.step_id,
        js.command,
        j.enabled 
    FROM    [msdb].dbo.sysjobs j
    JOIN    [msdb].dbo.sysjobsteps js
        ON  js.job_id = j.job_id 
    JOIN    master.dbo.sysservers s
        ON  s.srvid = j.originating_server_id
    WHERE   js.command LIKE @search
END

ELSE BEGIN 
    SELECT o.name As ''Stored Procedures''
    FROM SYSOBJECTS o 
    INNER JOIN SYSCOMMENTS c ON o.id = c.id
    WHERE c.text LIKE @search
        AND o.xtype IN (''P'',''FN'')
    GROUP BY o.name
    ORDER BY o.name

    SELECT o.name As ''Views''
    FROM SYSOBJECTS o 
    INNER JOIN SYSCOMMENTS c ON o.id = c.id
    WHERE c.text LIKE @search
        AND o.xtype = ''V''
    GROUP BY o.name
    ORDER BY o.name

    SELECT t.name AS ''Tables''
    FROM sys.columns c 
    JOIN sys.tables t ON c.object_id = t.object_id
    WHERE c.name LIKE @search
    ORDER BY Tables

    SELECT  j.name AS ''Jobs''
    FROM    [msdb].dbo.sysjobs j
    JOIN    [msdb].dbo.sysjobsteps js
        ON  js.job_id = j.job_id 
    JOIN    master.dbo.sysservers s
        ON  s.srvid = j.originating_server_id
    WHERE   js.command LIKE @search
END
'

EXECUTE sp_executesql @TSQL

更新: 如果您重命名了一个过程，它只更新sysobjects，而不更新syscomments，这将保留旧的名称，因此该过程将不会包括在搜索结果中，除非您删除并重新创建该过程。

请把这作为一个“肮脏”的替代方案，但这挽救了我的落后很多次，特别是当我不熟悉DB项目。有时，你试图在所有sp中搜索一个字符串，却忘记了一些相关的逻辑可能隐藏在函数和触发器之间，或者它可能只是措辞与你想象的不同。

从您的MSSMS中，您可以右键单击您的DB并选择 生成脚本向导，将所有sp, Fns和触发器输出到单个.sql文件。

确保也选择了触发器!

然后只需使用Sublime或Notepad来搜索你需要找到的字符串。我知道这可能是非常低效和偏执的方法，但它有效:)

我经常使用这个脚本来确定要修改哪个procs，或者确定使用表中的某一列，或者删除一些旧的垃圾。它检查由sp_msforeachdb提供的运行实例上的每个数据库。

if object_id('tempdb..##nothing') is not null
    drop table ##nothing

CREATE TABLE ##nothing
(
    DatabaseName varchar(30),
    SchemaName varchar(30),
    ObjectName varchar(100),
    ObjectType varchar(50)
)

EXEC master.sys.sp_msforeachdb 
'USE ?
insert into ##nothing
SELECT 
db_name() AS [Database],
[Scehma]=schema_name(o.schema_id), 
o.Name, 
o.type 
FROM sys.sql_modules m
INNER JOIN sys.objects o
    ON o.object_id = m.object_id
WHERE 
    m.definition like ''%SOME_TEXT%'''  
--edit this text

SELECT * FROM ##nothing n
order by OBJECTname 

SELECT DISTINCT 
   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 WHERE m.definition Like '%[String]%';