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

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)