Get maintenance plans info and It's by effect jobs


I have been wonder how to get info (by query NOT EM) of all the Maintenance Plans and it's by effect jobs plus schedule. I also see people asking the same questions in different forums. Finally, I start exploring myself (with helps from others of course), here's the result.
You can run it as a ad-hoc query or create it as SP (with little bit of code effort)

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)

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
set @counter = 0
set @x = 64
set @y = @freq_interval
set @z = @y
set @days = ''
set @day = ''

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

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'

select @days = @day + ',' + @days
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

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

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

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, as [Job_Name],
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'
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'
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    
        sj.job_id = sp.job_id               
join 	msdb.dbo.sysdbmaintplan_databases sd
	sp.plan_id = sd.plan_id 
join 	msdb.dbo.sysdbmaintplans smp
	sd.plan_id = smp.plan_id
join	msdb.dbo.sysjobs sjobs
join	msdb.dbo.sysjobschedules jobsch
left outer join #temp c 
	sp.job_id = c.jid

order by 1

Drop Table #Temp