---查看job的基本信息
select  a.job_id,a.name, a.date_created ,a.date_modified ,case when a.enabled='1' then N'是'when a.enabled='0' then N'否' end as enabled ,a.description,  b.step_id,b.step_name,b.subsystem,b.command,b.database_name,b.last_run_datefrom msdb.dbo.sysjobs a  left join msdb.dbo.sysjobsteps  b on a.job_id=b.job_id where  a.enabled='1'  order by a.job_id,b.step_id  ---对比select b.* ,a.*,
case when   sec_c =  pri_c then '相同' when  pri_c is not null and sec_c is null then '需要新增'else  '差异'
end as resultfrom 
( select   a.name  as sec_name,a.step_id as sec_stepid, a.step_name as sec_stepname,cast (a.command as nvarchar(1000))  as sec_command, a.database_name as sec_dbname,a.name+  cast(  cast( a.step_id as varchar(2))+ a.step_name+a.subsystem+a.command+ isnull(database_name,'test') as   nvarchar(2000)) as sec_c from job_sec a) a full join (  select b.name as pri_name,b.step_id as pri_stepid, b.step_name as pri_stepname,cast (b.command as nvarchar(1000)) as pri_command,b.database_name as pri_dbname,cast(  b.name+ cast( b.step_id as varchar(2))+ b.step_name+b.subsystem+b.command+ isnull(database_name,'test') as   nvarchar(2000))    as pri_cfrom job_job b) b on  sec_c=  pri_corder by  case when   sec_c =  pri_c then '相同' when  pri_c is not null and sec_c is null then '需要新增'else  '差异'
end 

通过第一个sql查询job的基本信息导出为excel， 然后分别把主从库的job信息导入到表 job_pri,job_sec,然后通过第二条语句对比