Select Cast(@@ServerName As varchar(20)) AS [DB Server Instance] , DB_Name(database_id) As [DB Name] , Stuff(job_type, 1, 1, Upper(SubString(job_type, 1, 1))) As [Job Type] , sj.name As [Job Name] , sj.description As [Job Description] , SUser_Name(sj.owner_sid) As [Job Owner] , Case When job_type = 'capture' Then '' Else Cast(retention As varchar(20)) + ' minutes' End As [CDC Retention] , Case When job_type = 'capture' Then '' Else Cast(Cast((retention + 0.) / 60. As decimal(9, 2)) As varchar(20)) + ' hours' End As [CDC Retention] , Case When job_type = 'capture' Then '' Else Cast(Cast((retention + 0.) / (60. * 24.) As decimal(9, 2)) As varchar(20)) + ' days' End As [CDC Retention] , Case When job_type = 'capture' Then '' Else Cast(threshold As varchar(20)) + ' rows' End As [Threshold (per delete SQL statement)] , Case When job_type = 'capture' Then Cast(continuous As varchar(5)) + ' - ' + Case When continuous = 1 Then 'Yes' Else 'No' End Else '' End As [Continuous?] , Case When job_type = 'capture' Then Cast(maxtrans As varchar(20)) + ' transactions' Else '' End As [Max. Transactions per scan cycle] , Case When job_type = 'capture' Then Cast(maxscans As varchar(20)) + ' scans' Else '' End As [Max. # Scans] , Case When job_type = 'capture' Then Cast(pollinginterval As varchar(20)) + ' seconds' Else '' End As [Polling Interval - between log scan cycles] , cj.* , sj.* From msdb.dbo.cdc_jobs cj Left Join msdb.dbo.sysjobs sj On (cj.job_id = sj.job_id)
