在 SQL Server 的存储过程中搜索文本

Question

我想从我的所有数据库存储过程中搜索文本。我使用下面的 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] ，但它没有给出正确的结果。如何更改查询以实现此目的？

原文由 DharaPPatel 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

转义方括号：

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

然后方括号将被视为字符串文字而不是通配符。

原文由 Mahmoud Gamal 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 2

此搜索例程基于 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'

原文由 gil.fernandes 发布，翻译遵循 CC BY-SA 4.0 许可协议