我想从我所有的数据库存储过程中搜索一个文本。我使用下面的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 , type_desc , create_date , modify_date
FROM   sys.procedures 
WHERE  Object_definition(object_id) LIKE '%High%'

其他回答

你也可以使用

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

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

END

然后运行

exec [Search] 'text'

你也可以用这个:

SELECT * 
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_DEFINITION like '%Search_String%'

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

select distinct object_name(id) 
from syscomments 
where text like '%[ABD]%'
order by object_name(id) 
 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 =视图

表函数

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