我想从我所有的数据库存储过程中搜索一个文本。我使用下面的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 distinct object_name(id)
from syscomments
where text like '%[ABD]%'
order by object_name(id)
其他回答
你还可以用:
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'
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 '%[String]%';
我通常执行以下命令来实现这一目标:
select distinct object_name(id)
from syscomments
where text like '%[ABD]%'
order by object_name(id)
它可能对你有帮助!
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
