2

I recently saw some kind of export of SQL jobs configured under SQL Server Agent that I would like to achieve as well. Unfortunately I don't have any trace to author of that report to ask him\her about report creation, hence looking for help here as I had no luck googling it nor searching in here.

Report was in excel (so I guess originally was some csv or maybe directly xls) and contained every single job from SQL instance with one step per row and most importantly containing also script content of each job step and some further details.

As for an example as SQL has default job syspolicy_purge_history only, export will contain 3 rows for each of it's step.

I can't seem to find it as any build in export, so I guess some query maybe or I have missed this somewhere

Anyone aware of any such report? Any help is welcome

3
  • They probably used PowerShell to pull the information from msdb.dbo.sysjobs, msdb.dbo.sysjobsteps, msdb.dbo.sysjobhistory, etc. Feb 2, 2015 at 21:04
  • PowerShell is good call, honestly forgot to check here for possibilities... certainly will have to dig through PowerShell cmdlets - thanks for reminder on this
    – R1cky
    Feb 2, 2015 at 21:23
  • Yeah, export from SSMS either to file or copy/paste is not going to format into Excel correctly. It is more likely they handled friendly formatting for that information outside of SQL Server.
    – user507
    Feb 2, 2015 at 22:08

2 Answers 2

7

Would something like this work?

USE [msdb]
GO
SELECT 
    j.Name 
    , j.[Description] 
    , js.Step_ID 
    , js.Step_Name
    , js.Database_Name 
    , js.Command 
    --, j.*, js.* 
FROM dbo.sysjobs j
INNER JOIN dbo.sysjobsteps js
    ON js.job_id = j.job_id 
--WHERE j.enabled = 1
ORDER BY j.name, js.Step_ID 
1
  • 1
    Command is unlikely to get pulled into Excel very nicely. Feb 2, 2015 at 21:27
1
USE [msdb]
GO
SELECT 
    j.Name as Job_name
    ,case j.enabled
        when 1 then 'yes'
        when 0 then 'no'
    end as [isJobenabled]
    , j.category_id  as category_Of_Job
    , j.[Description] as Job_Description
    , js.Step_ID as Job_Step_Id
    , js.Step_Name as Job_Step_name
    , js.subsystem as Step_Type
    , js.Command as Excutable_Command
    , js.Database_Name
    , js.server
    , js.output_file_name
    , case sySch.enabled 
        when 1 then 'yes'
        when 0 then 'no'
    end as [isJobschedule_enabled] 
    , CASE 
        WHEN [freq_type] = 64 THEN 'Start automatically when SQL Server Agent starts'
        WHEN [freq_type] = 128 THEN 'Start whenever the CPUs become idle'
        WHEN [freq_type] IN (4,8,16,32) THEN 'Recurring'
        WHEN [freq_type] = 1 THEN 'One Time'
      END [ScheduleType]
    , CASE [freq_type]
        WHEN 1 THEN 'One Time'
        WHEN 4 THEN 'Daily'
        WHEN 8 THEN 'Weekly'
        WHEN 16 THEN 'Monthly'
        WHEN 32 THEN 'Monthly - Relative to Frequency Interval'
        WHEN 64 THEN 'Start automatically when SQL Server Agent starts'
        WHEN 128 THEN 'Start whenever the CPUs become idle'
      END [Occurrence]
    , CASE [freq_type]
        WHEN 4 THEN 'Occurs every ' + CAST([freq_interval] AS VARCHAR(3)) + ' day(s)'
        WHEN 8 THEN 'Occurs every ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) 
                    + ' week(s) on '
                    + CASE WHEN [freq_interval] & 1 = 1 THEN 'Sunday' ELSE '' END
                    + CASE WHEN [freq_interval] & 2 = 2 THEN ', Monday' ELSE '' END
                    + CASE WHEN [freq_interval] & 4 = 4 THEN ', Tuesday' ELSE '' END
                    + CASE WHEN [freq_interval] & 8 = 8 THEN ', Wednesday' ELSE '' END
                    + CASE WHEN [freq_interval] & 16 = 16 THEN ', Thursday' ELSE '' END
                    + CASE WHEN [freq_interval] & 32 = 32 THEN ', Friday' ELSE '' END
                    + CASE WHEN [freq_interval] & 64 = 64 THEN ', Saturday' ELSE '' END
        WHEN 16 THEN 'Occurs on Day ' + CAST([freq_interval] AS VARCHAR(3)) 
                     + ' of every '
                     + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)'
        WHEN 32 THEN 'Occurs on '
                     + CASE [freq_relative_interval]
                        WHEN 1 THEN 'First'
                        WHEN 2 THEN 'Second'
                        WHEN 4 THEN 'Third'
                        WHEN 8 THEN 'Fourth'
                        WHEN 16 THEN 'Last'
                       END
                     + ' ' 
                     + CASE [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
                     + ' of every ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) 
                     + ' month(s)'
      END AS [Recurrence]
    , CASE [freq_subday_type]
        WHEN 1 THEN 'Occurs once at ' 
                    + STUFF(
                 STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
        WHEN 2 THEN 'Occurs every ' 
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Second(s) between ' 
                    + STUFF(
                   STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
                    + ' & ' 
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
        WHEN 4 THEN 'Occurs every ' 
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Minute(s) between ' 
                    + STUFF(
                   STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
                    + ' & ' 
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
        WHEN 8 THEN 'Occurs every ' 
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Hour(s) between ' 
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
                    + ' & ' 
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
                                , 3, 0, ':')
                            , 6, 0, ':')
      END [Frequency] 
    --, j.*, js.* 
FROM dbo.sysjobs j
INNER JOIN dbo.sysjobsteps js
    ON js.job_id = j.job_id 
--WHERE j.enabled = 1
left join sysjobschedules syJsch
   on syJsch.job_id=js.job_id
left join sysschedules sySch
   on sySch.schedule_id=syJsch.schedule_id
ORDER BY j.name, js.Step_ID 
1
  • It would be good to clarify why this is a better answer than the accepted one. As far as I can see, you went through the effort of adding in schedule data (which could be useful, but was not explicitly requested by the OP). Note, of course, that jobs with multiple schedules will result in multiple rows for each step. And, this would have the same issues with being brought into Excel noted by @AaronBertrand.
    – RDFozz
    Oct 19, 2018 at 20:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.