1

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

  • They probably used PowerShell to pull the information from msdb.dbo.sysjobs, msdb.dbo.sysjobsteps, msdb.dbo.sysjobhistory, etc. – Aaron Bertrand Feb 2 '15 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 – Ricky-Brno Feb 2 '15 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. – Shawn Melton Feb 2 '15 at 22:08
6

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
    Command is unlikely to get pulled into Excel very nicely. – Aaron Bertrand Feb 2 '15 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 
  • 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 '18 at 20:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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