查询符合条件的 sql agent job

use msdb
go

--if object_id('tempdb..#SqlAgentJob') is not null
--	drop table #SqlAgentJob
--go

declare @SqlAgentJob table(
[job_id] uniqueidentifier
,[originating_server] nvarchar(30)
,[name] sysname null
,[enabled] tinyint
,[description] nvarchar(512)
,[start_step_id] int
,[category] sysname null
,[owner] sysname null
,[notify_level_eventlog] int
,[notify_level_email] int
,[notify_level_netsend] int
,[notify_level_page] int
,[notify_email_operator] sysname null
,[notify_netsend_operator] sysname null
,[notify_page_operator] sysname null
,[delete_level] int
,[date_created] datetime
,[date_modified] datetime
,[version_number] int
,[last_run_date] int
,[last_run_time] int
,[last_run_outcome] int
,[next_run_date] int
,[next_run_time] int
,[next_run_schedule_id] int
,[current_execution_status] int
,[current_execution_step] sysname null
,[current_retry_attempt] int
,[has_step] int
,[has_schedule] int
,[has_target] int
,[type] int
)

--go

INSERT INTO @SqlAgentJob
(
[job_id]
,[originating_server]
,[name]
,[enabled]
,[description]
,[start_step_id]
,[category]
,[owner]
,[notify_level_eventlog]
,[notify_level_email]
,[notify_level_netsend]
,[notify_level_page]
,[notify_email_operator]
,[notify_netsend_operator]
,[notify_page_operator]
,[delete_level]
,[date_created]
,[date_modified]
,[version_number]
,[last_run_date]
,[last_run_time]
,[last_run_outcome]
,[next_run_date]
,[next_run_time]
,[next_run_schedule_id]
,[current_execution_status]
,[current_execution_step]
,[current_retry_attempt]
,[has_step]
,[has_schedule]
,[has_target]
,[Type]
)
EXECUTE sp_help_job 
 
--select * from @SqlAgentJob 
 
select * from @SqlAgentJob where name like '%humana%'
-- and isnull([owner],'')='' and enabled=1
 

 

"sp_help_job"  MSDN ADDRESS: http://technet.microsoft.com/zh-cn/library/aa933458(v=sql.80).aspx