[已解决]查询以查找职位定义中的文本? [英] [Solved] Query to find text in a job definition?

问题描述

我有一个查询，该查询搜索数据库中的对象并返回包含文本的那些对象的名称:

I have a query that searches objects within a database and returns the name of those objects containing text:

SELECT o.Name
FROM sys.sql_modules m INNER JOIN sysobjects o ON m.object_id=o.id
WHERE m.Definition LIKE '%search text%'

我需要一个类似的查询，该查询将搜索SQL Server代理中的作业定义，这些作业没有绑定到特定的数据库.作业存储在msdb.dbo.sysjobs中，并且具有作业ID，而不是对象ID.

是的，这是一个给定的代码"问题，但是我有点儿束缚在这里. :doh:

I need a similar query that will search the definitions of jobs in SQL Server Agent, which are not tied to a particular database. Jobs are stored in msdb.dbo.sysjobs and have job ids, not object ids.

Yeah, this is a "gimme code" question, but I''m kind of in a bind here. :doh:

推荐答案

找到了答案.奇怪的是，作业中的步骤称为表sysjobsteps.它具有称为command的列.我需要的查询是

Found the answer. The steps in a job are in a table called, oddly enough, sysjobsteps. It has a column called command. The query I need is

SELECT jobs.Name
FROM msdb.dbo.sysjobs jobs LEFT OUTER JOIN msdb.dbo.sysjobsteps steps
    ON jobs.job_id=steps.job_id
WHERE steps.command LIKE '%search text%'

这篇关于[已解决]查询以查找职位定义中的文本?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！