我想从我所有的数据库存储过程中搜索一个文本。我使用下面的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
其他回答
你也可以用这个:
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION like '%Search_String%'
Select distinct OBJECT_NAME(id) from syscomments where text like '%string%' AND OBJECTPROPERTY(id, 'IsProcedure') = 1
我通常执行以下命令来实现这一目标:
select distinct object_name(id)
from syscomments
where text like '%[ABD]%'
order by object_name(id)
不同的版本，使查询更适合不同的编码实践。
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 UPPER(M.DEFINITION) LIKE UPPER('%Your 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]%';
