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)