使用步驟和腳本導出 SQL 代理作業

我最近看到了在 SQL Server 代理下配置的某種 SQL 作業的導出，我也想實現。不幸的是，我沒有找到該報告的作者向他\她詢問報告創建的任何線索，因此在這裡尋求幫助，因為我沒有運氣在Google上搜尋它，也沒有在這裡搜尋。

報告在 excel 中（所以我猜最初是一些 csv 或者可能直接是 xls）並包含來自 SQL 實例的每一個作業，每行一個步驟，最重要的是還包含每個作業步驟的腳本內容和一些進一步的細節。

舉個例子，因為 SQL 僅具有預設作業 syspolicy_purge_history，export 的每個步驟將包含 3 行。

我似乎無法在導出中找到它，所以我猜可能有一些查詢，或者我在某處錯過了這個

有人知道這樣的報導嗎？歡迎任何幫助

像這樣的東西會起作用嗎？

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 

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 

引用自：https://dba.stackexchange.com/questions/90852