我想从我所有的数据库存储过程中搜索一个文本。我使用下面的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
FROM sys.procedures
WHERE Object_definition(object_id) LIKE '%strHell%'
其他回答
转义方括号:
...
WHERE m.definition Like '%\[ABD\]%' ESCAPE '\'
那么方括号将被视为字符串字面量，而不是通配符。
我经常使用这个脚本来确定要修改哪个procs，或者确定使用表中的某一列，或者删除一些旧的垃圾。它检查由sp_msforeachdb提供的运行实例上的每个数据库。
if object_id('tempdb..##nothing') is not null
drop table ##nothing
CREATE TABLE ##nothing
(
DatabaseName varchar(30),
SchemaName varchar(30),
ObjectName varchar(100),
ObjectType varchar(50)
)
EXEC master.sys.sp_msforeachdb
'USE ?
insert into ##nothing
SELECT
db_name() AS [Database],
[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 ''%SOME_TEXT%'''
--edit this text
SELECT * FROM ##nothing n
order by OBJECTname
/*
SEARCH SPROCS & VIEWS
The following query will allow search within the definitions
of stored procedures and views.
It spits out the results as XML, with the full definitions,
so you can browse them without having to script them individually.
*/
/*
STEP 1: POPULATE SEARCH KEYS. (Set to NULL to ignore)
*/
DECLARE
@def_key varchar(128) = '%foo%', /* <<< definition search key */
@name_key varchar(128) = '%bar%', /* <<< name search key */
@schema_key varchar(128) = 'dbo'; /* <<< schema search key */
;WITH SearchResults AS (
/*
STEP 2: DEFINE SEARCH QUERY AS CTE (Common Table Expression)
*/
SELECT
[Object].object_id AS [object_id],
[Schema].name AS [schema_name],
[Object].name AS [object_name],
[Object].type AS [object_type],
[Object].type_desc AS [object_type_desc],
[Details].definition AS [module_definition]
FROM
/* sys.sql_modules = where the body of sprocs and views live */
sys.sql_modules AS [Details] WITH (NOLOCK)
JOIN
/* sys.objects = where the metadata for every object in the database lives */
sys.objects AS [Object] WITH (NOLOCK) ON [Details].object_id = [Object].object_id
JOIN
/* sys.schemas = where the schemas in the datatabase live */
sys.schemas AS [Schema] WITH (NOLOCK) ON [Object].schema_id = [Schema].schema_id
WHERE
(@def_key IS NULL OR [Details].definition LIKE @def_key) /* <<< searches definition */
AND (@name_key IS NULL OR [Object].name LIKE @name_key) /* <<< searches name */
AND (@schema_key IS NULL OR [Schema].name LIKE @schema_key) /* <<< searches schema */
)
/*
STEP 3: SELECT FROM CTE INTO XML
*/
/*
This outer select wraps the inner queries in to the <sql_object> root element
*/
SELECT
(
/*
This inner query maps stored procedure rows to <procedure> elements
*/
SELECT TOP 100 PERCENT
[object_id] AS [@object_id],
[schema_name] + '.' + [object_name] AS [@full_name],
[module_definition] AS [module_definition]
FROM
SearchResults
WHERE
object_type = 'P'
ORDER BY
[schema_name], [object_name]
FOR XML
PATH ('procedure'), TYPE
) AS [procedures], /* <<< as part of the outer query,
this alias causes the <procedure> elements
to be wrapped within the <procedures> element */
(
/*
This inner query maps view rows to <view> elements
*/
SELECT TOP 100 PERCENT
[object_id] AS [@object_id],
[schema_name] + '.' + [object_name] AS [@full_name],
[module_definition] AS [module_definition]
FROM
SearchResults
WHERE
object_type = 'V'
ORDER BY
[schema_name], [object_name]
FOR XML
PATH ('view'), TYPE
) AS [views] /* <<< as part of the outer query,
this alias causes the <view> elements
to be wrapped within the <views> element */
FOR XML
PATH ('sql_objects')
我创建了一个过程来搜索过程/函数、表、视图或作业中的文本。第一个参数@search是搜索条件，@target是搜索目标，即过程、表等。如果未指定，则搜索全部。@db是指定要搜索的数据库，默认为当前数据库。下面是我在动态SQL中的查询。
ALTER PROCEDURE [dbo].[usp_find_objects]
(
@search VARCHAR(255),
@target VARCHAR(255) = NULL,
@db VARCHAR(35) = NULL
)
AS
SET NOCOUNT ON;
DECLARE @TSQL NVARCHAR(MAX), @USEDB NVARCHAR(50)
IF @db <> '' SET @USEDB = 'USE ' + @db
ELSE SET @USEDB = ''
IF @target IS NULL SET @target = ''
SET @TSQL = @USEDB + '
DECLARE @search VARCHAR(128)
DECLARE @target VARCHAR(128)
SET @search = ''%' + @search + '%''
SET @target = ''' + @target + '''
IF @target LIKE ''%Procedure%'' BEGIN
SELECT o.name As ''Stored Procedures''
FROM SYSOBJECTS o
INNER JOIN SYSCOMMENTS c ON o.id = c.id
WHERE c.text LIKE @search
AND o.xtype IN (''P'',''FN'')
GROUP BY o.name
ORDER BY o.name
END
ELSE IF @target LIKE ''%View%'' BEGIN
SELECT o.name As ''Views''
FROM SYSOBJECTS o
INNER JOIN SYSCOMMENTS c ON o.id = c.id
WHERE c.text LIKE @search
AND o.xtype = ''V''
GROUP BY o.name
ORDER BY o.name
END
/* Table - search table name only, need to add column name */
ELSE IF @target LIKE ''%Table%'' BEGIN
SELECT t.name AS ''TableName''
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE @search
ORDER BY TableName
END
ELSE IF @target LIKE ''%Job%'' BEGIN
SELECT j.job_id,
s.srvname,
j.name,
js.step_id,
js.command,
j.enabled
FROM [msdb].dbo.sysjobs j
JOIN [msdb].dbo.sysjobsteps js
ON js.job_id = j.job_id
JOIN master.dbo.sysservers s
ON s.srvid = j.originating_server_id
WHERE js.command LIKE @search
END
ELSE BEGIN
SELECT o.name As ''Stored Procedures''
FROM SYSOBJECTS o
INNER JOIN SYSCOMMENTS c ON o.id = c.id
WHERE c.text LIKE @search
AND o.xtype IN (''P'',''FN'')
GROUP BY o.name
ORDER BY o.name
SELECT o.name As ''Views''
FROM SYSOBJECTS o
INNER JOIN SYSCOMMENTS c ON o.id = c.id
WHERE c.text LIKE @search
AND o.xtype = ''V''
GROUP BY o.name
ORDER BY o.name
SELECT t.name AS ''Tables''
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE @search
ORDER BY Tables
SELECT j.name AS ''Jobs''
FROM [msdb].dbo.sysjobs j
JOIN [msdb].dbo.sysjobsteps js
ON js.job_id = j.job_id
JOIN master.dbo.sysservers s
ON s.srvid = j.originating_server_id
WHERE js.command LIKE @search
END
'
EXECUTE sp_executesql @TSQL
更新: 如果您重命名了一个过程，它只更新sysobjects，而不更新syscomments，这将保留旧的名称，因此该过程将不会包括在搜索结果中，除非您删除并重新创建该过程。
你也可以使用
CREATE PROCEDURE [Search](
@Filter nvarchar(max)
)
AS
BEGIN
SELECT name
FROM procedures
WHERE definition LIKE '%'+@Filter+'%'
END
然后运行
exec [Search] 'text'
