sql 测试脚本 sql agent job / control-m job

最新推荐文章于 2025-10-14 12:10:15 发布

原创最新推荐文章于 2025-10-14 12:10:15 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

Sql Server 专栏收录该内容

125 篇文章

订阅专栏

本文介绍了一段SQL脚本，该脚本用于查询SQL Server中的作业（Jobs）信息及Control-M作业定义。根据设置的参数，可以筛选特定包名、SQL作业名或Control-M作业名的相关记录。

declare @sPackageName varchar(50)
	set @sPackageName=''
declare @sSqlJobName varchar(50)
	set @sSqlJobName=''
declare @sControlMJobName varchar(50)
	set @sControlMJobName=''

declare @sSQL varchar(max)
	set @sSQL=''
declare @sTestKind int
	set @sTestKind=1 -- 0:select sql job; 1:select control-m job

if(@sTestKind=0)
begin
	set @sSQL='
	SELECT 
		b.[name] [JobName]
		,CASE WHEN b.enabled=1 THEN ''Yes'' ELSE ''No'' END [Enabled] 
		,b.description [JobDescription]
		,a.step_name [StepName]
		,a.command [Script] 
	FROM msdb.dbo.sysjobsteps a 
	INNER JOIN msdb.dbo.sysjobs b 
		ON a.job_id=b.job_id '
	
	if(rtrim(ltrim(@sPackageName))!='')
	begin
		set @sSQL=@sSQL +
		'
		WHERE a.command LIKE ''%' + upper(@sPackageName) + '%'''
	end
	else if(rtrim(ltrim(@sSqlJobName))!='')
	begin
		set @sSQL=@sSQL +
		'
		WHERE b.Name LIKE ''%' + upper(@sSqlJobName) + '%'''
	end
end
else if(@sTestKind=1)
begin
	set @sSQL='
	SELECT  * 
	FROM [CTRLM].[dbo].[CMS_JOBDEF] ctrm '
	
	if(rtrim(ltrim(@sSqlJobName))!='')
	begin
		set @sSQL=@sSQL +
		'
		WHERE ctrm.CMDLINE like ''%' + upper(@sSqlJobName) + '%'''
	end
	else if(rtrim(ltrim(@sControlMJobName))!='')
	begin
		set @sSQL=@sSQL +
		'
		WHERE JobName LIKE ''%' + upper(@sControlMJobName) + '%'''
	end
end

print @sSQL
exec(@sSQL)