我想从我所有的数据库存储过程中搜索一个文本。我使用下面的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],但它没有给出正确的结果。我如何改变我的查询来实现这一点?
这个搜索例程基于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'
下面是一个替代方法,它在一个查询中列出了所有具有特定关键字的对象:
SELECT DISTINCT
s.name AS Schema_Name,
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
INNER JOIN sys.schemas s on o.schema_id = s.schema_id
WHERE m.definition Like '%dim_forex%' ESCAPE '\' order by 3;
此查询应返回与应答中的过程调用相同的结果。