Get maintenance plans info and It's by effect jobs

declare @x int, @y int, @z int
declare @counter smallint
declare @days varchar(100), @day varchar(10)
declare @Jname sysname, @freq_interval int, @JID varchar(50)
SET NOCOUNT ON

create table #temp (JID varchar(50), Jname sysname, Jdays varchar(100))

--This cursor runs throough all the jobs that have a weekly frequency running on different days
Declare C cursor for select Job_id, name, freq_interval from msdb..sysjobschedules
where freq_type = 8
Open C
Fetch Next from c into @JID, @Jname, @freq_interval
while @@fetch_status = 0
Begin
set @counter = 0
set @x = 64
set @y = @freq_interval
set @z = @y
set @days = ''
set @day = ''

while @y <> 0
begin
select @y = @y - @x
select @counter = @counter + 1
If @y < 0 
Begin
set @y = @z
GOTO start
End


Select @day = CASE @x
when 1 Then 'Sunday'
when 2 Then 'Monday'
when 4 Then 'Tuesday'
when 8 Then 'Wednesday'
when 16 Then 'Thursday'
when 32 Then 'Friday'
when 64 Then 'Saturday'
End

select @days = @day + ',' + @days
start:
Select @x = CASE @counter
When 1 then 32
When 2 then 16
When 3 then 8
When 4 then 4
When 5 then 2
When 6 then 1
End

set @z = @y
if @y = 0 break
end

Insert into #temp select @jid, @jname, left(@days, len(@days)-1)
Fetch Next from c into @jid, @Jname, @freq_interval

End
close c
deallocate c


--Final query to extract complete maint plan info by joining sysjobs, sysjobschedules and #Temp table

SELECT  smp.plan_name as [Maint_Plan_Name], substring(sj.command,80,100) as command , sd.database_name , smp.date_created as [Maint_Plan created],                      
--sj.job_id,
sjobs.name as [Job_Name],
--jobsch.freq_type
CASE jobsch.freq_type 
when 1 Then 'Once'
when 4 Then 'Daily'
when 8 then 'Weekly'
when 16 Then 'Monthly' --+ cast(freq_interval as char(2)) + 'th Day'
when 32 Then 'Monthly Relative'
when 64 Then 'Execute When SQL Server Agent Starts'
End as [Job Frequency],

CASE jobsch.freq_type
when 16 then cast(jobsch.freq_interval as char(2)) + 'th Day of Month'
when 32 then CASE jobsch.freq_interval 
when 1 then 'Sunday'
when 2 then 'Monday'
when 3 then 'Tuesday'
when 4 then 'Wednesday'
when 5 then 'Thursday'
when 6 then 'Friday'
when 7 then 'Saturday'
when 8 then 'Day'
when 9 then 'Weekday'
when 10 then 'Weekend day'
End
when 8 then c.Jdays
Else ''
End as [Runs On],

CASE jobsch.freq_type 
when 32 then CASE jobsch.freq_relative_interval
when 1 then 'First'
when 2 then 'Second'
when 4 then 'Third'
when 8 then 'Fourth'
when 16 then 'Last'
End
Else ''
End as [Monthly Frequency (if it's Monthly Job)],

CASE jobsch.freq_subday_type 
when 1 then 0
Else jobsch.freq_subday_interval 
End as [Time Interval],

CASE jobsch.freq_subday_type
when 1 then 'At the specified Time'
when 2 then 'Seconds'
when 4 then 'Minutes'
when 8 then 'Hours'
End as [Interval Type], 

CASE jobsch.freq_type 
when 8 then cast(jobsch.freq_recurrence_factor as char(2)) + ' Week'
when 16 Then cast(jobsch.freq_recurrence_factor as char(2)) + ' Month'
when 32 Then cast(jobsch.freq_recurrence_factor as char(2)) + ' Month'
Else ''
End as [Recurring Every],

left(REPLICATE('0', 6-len(jobsch.active_start_time)) + cast(jobsch.active_start_time as char(6)),2) + ':' +
substring(REPLICATE('0', 6-len(jobsch.active_start_time)) + cast(jobsch.active_start_time as char(6)),3,2) + ':' +
substring(REPLICATE('0', 6-len(jobsch.active_start_time)) + cast(jobsch.active_start_time as char(6)),5,2)
as [Start Executing At],

left(REPLICATE('0', 6-len(jobsch.active_end_time)) + cast(jobsch.active_end_time as char(6)),2) + ':' +
substring(REPLICATE('0', 6-len(jobsch.active_end_time)) + cast(jobsch.active_end_time as char(6)),3,2) + ':' +
substring(REPLICATE('0', 6-len(jobsch.active_end_time)) + cast(jobsch.active_end_time as char(6)),5,2)
as [End Executing At],

left(jobsch.active_start_date,4) + '-' + substring(cast(jobsch.active_start_date as char),5,2) 
+ '-' + right(jobsch.active_start_date,2) as [Begin Date-Executing Job], 

left(jobsch.active_end_date,4) + '-' + substring(cast(jobsch.active_end_date as char),5,2) 
+ '-' + right(jobsch.active_end_date,2) as [End Date-Executing Job]

FROM    msdb.dbo.sysjobsteps          sj 
join    msdb.dbo.sysdbmaintplan_jobs  sp    
on              
        sj.job_id = sp.job_id               
join msdb.dbo.sysdbmaintplan_databases sd
on 
sp.plan_id = sd.plan_id 
join msdb.dbo.sysdbmaintplans smp
on 
sd.plan_id = smp.plan_id
joinmsdb.dbo.sysjobs sjobs
on 
sp.job_id=sjobs.job_id
joinmsdb.dbo.sysjobschedules jobsch
on 
sp.job_id=jobsch.job_id
left outer join #temp c 
on 
sp.job_id = c.jid

order by 1

Drop Table #Temp