我想从我所有的数据库存储过程中搜索一个文本。我使用下面的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 ROUTINE_NAME 
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION like '%\[ABD\]%'

其他回答

Select distinct OBJECT_NAME(id) from syscomments where text like '%string%' AND OBJECTPROPERTY(id, 'IsProcedure') = 1 

还可以试试这个:

   SELECT ROUTINE_NAME 
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION like '%\[ABD\]%'
SELECT name , type_desc , create_date , modify_date
FROM   sys.procedures 
WHERE  Object_definition(object_id) LIKE '%High%'

你还可以用:

SELECT OBJECT_NAME(id) 
    FROM syscomments 
    WHERE [text] LIKE '%flags.%' 
    AND OBJECTPROPERTY(id, 'IsProcedure') = 1 
    GROUP BY OBJECT_NAME(id)

包括评论

你也可以使用

CREATE PROCEDURE [Search](
    @Filter nvarchar(max)
)
AS
BEGIN

SELECT name
FROM   procedures
WHERE   definition LIKE '%'+@Filter+'%'

END

然后运行

exec [Search] 'text'