我想从我所有的数据库存储过程中搜索一个文本。我使用下面的SQL:

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

我想在包括方括号在内的所有存储过程中搜索[ABD],但它没有给出正确的结果。我如何改变我的查询来实现这一点?


当前回答

试试这个请求:

查询

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

其他回答

使用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 OBJECT_NAME(id) 
    FROM syscomments 
    WHERE [text] LIKE '%flags.%' 
    AND OBJECTPROPERTY(id, 'IsProcedure') = 1 
    GROUP BY OBJECT_NAME(id)

包括评论

-- Applicable for SQL 2005+
USE YOUR_DATABASE_NAME //;
    GO

SELECT [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 '%YOUR SEARCH KEYWORDS%'
GO

转义方括号:

...
WHERE m.definition Like '%\[ABD\]%' ESCAPE '\'

那么方括号将被视为字符串字面量,而不是通配符。

试试这个请求:

查询

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