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]%';

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

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

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

表函数

使用SQL Server的好方法。

创建以下存储过程并设置短键，

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

    --TO FIND STRING IN ALL VIEWS        
    BEGIN
        SELECT OBJECT_NAME(OBJECT_ID) View_Name
              ,OBJECT_DEFINITION(OBJECT_ID) View_Definition
        FROM   sys.views
        WHERE  OBJECT_DEFINITION(OBJECT_ID) LIKE '%'+@strFind+'%'
    END 

    --TO FIND STRING IN ALL FUNCTION        
    BEGIN
        SELECT 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 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
        WHERE  c.name LIKE '%'+@strFind+'%'
        ORDER BY
               Table_Name
    END
END

现在-设置短键如下所示，

下次当你想在存储过程，视图，函数和表格这四个对象中找到一个特定的文本时。你只需要写下那个关键字，然后按快捷键。

例如:我想搜索“PaymentTable”，然后写“PaymentTable”，并确保您在查询编辑器中选择或突出显示所写的关键字，然后按快捷键ctrl+4 -它将为您提供完整的结果。

select top 10 * from
sys.procedures
where object_definition(object_id) like '%\[ABD\]%'

我通常执行以下命令来实现这一目标:

select distinct object_name(id) 
from syscomments 
where text like '%[ABD]%'
order by object_name(id)