我想从我所有的数据库存储过程中搜索一个文本。我使用下面的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],但它没有给出正确的结果。我如何改变我的查询来实现这一点?


当前回答

-- 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

其他回答

Redgate的SQL搜索是一个很好的工具,它是一个免费的SSMS插件。

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

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

确保也选择了触发器!

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

它可能对你有帮助!

SELECT DISTINCT 
      A.NAME AS OBJECT_NAME,
      A.TYPE_DESC
      FROM SYS.SQL_MODULES M 
      INNER JOIN SYS.OBJECTS A ON M.OBJECT_ID = A.OBJECT_ID
      WHERE M.DEFINITION LIKE '%['+@SEARCH_TEXT+']%'
      ORDER BY TYPE_DESC
select top 10 * from
sys.procedures
where object_definition(object_id) like '%\[ABD\]%'

这个搜索例程基于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;

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