1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
| USE msdb
GO
SELECT P.name AS nom_package
, P.createdate AS date_creation
, SP.name AS proprietaire_package
, CAST(CAST(P.packagedata AS varbinary(max)) AS XML) AS SSIS_xml
, J.name AS nom_job
, JS.step_name AS nom_etape
, JS.command AS commande
, JS.database_name AS contexte_BD
, JS.subsystem AS sous_systeme
, CONVERT(datetime, NR.next_run_date_time, 106) AS prochaine_execution
, CASE J.enabled WHEN 1 THEN 'OUI' ELSE 'NON' END AS job_actif
, CASE S.enabled WHEN 1 THEN 'OUI' ELSE 'NON' END AS job_planifie
, SPJ.name AS proprietaire_job
, CASE S.freq_type
WHEN 1 THEN 'Une seule fois'
WHEN 4 THEN 'Quotidiennement'
WHEN 8 THEN 'Hebdomadairement'
WHEN 16 THEN 'Mensuellement'
WHEN 32 THEN 'Mensuellement, à intervalles'
WHEN 64 THEN 'Lorsque le service SQL Server Agent démarre'
WHEN 128 THEN 'Lorsque le serveur est inactif'
END AS frequence
, CASE S.freq_type
WHEN 1 THEN 'Une seule fois'
WHEN 4 THEN 'Tous les' + CASE CONVERT(varchar, S.freq_interval) WHEN 1 THEN '' ELSE CONVERT(varchar, S.freq_interval) END + ' jours'
WHEN 8 THEN 'Toutes les' + CASE CONVERT(varchar, S.freq_recurrence_factor) WHEN 1 THEN '' ELSE CONVERT(varchar, S.freq_interval) END + ' semaines, le ' + D.freq_day_concat
WHEN 16 THEN 'Le ' + CONVERT(varchar, S.freq_interval) + ' de tous les '
+ CONVERT(varchar, S.freq_recurrence_factor) + ' mois'
WHEN 32 THEN 'Le ' + CASE S.freq_relative_interval
WHEN 1 THEN '1er'
WHEN 2 THEN '2e'
WHEN 4 THEN '3e'
WHEN 8 THEN '4e'
WHEN 16 THEN 'dernier'
END
+ ' '
+ CASE S.freq_interval
WHEN 1 THEN 'Dimanche'
WHEN 2 THEN 'Lundi'
WHEN 3 THEN 'Mardi'
WHEN 4 THEN 'Mercredi'
WHEN 5 THEN 'Jeudi'
WHEN 6 THEN 'Vendredi'
WHEN 7 THEN 'Samedi'
WHEN 8 THEN 'jour'
WHEN 9 THEN 'jour de semaine'
WHEN 10 THEN 'jour de weekend'
END
+ ' , tous les '
+ CONVERT(varchar, S.freq_recurrence_factor) + ' mois'
END + CASE S.freq_subday_type
WHEN 1 THEN ', une seule fois, à ' + AT.start_time
WHEN 2 THEN ', toutes les ' + CONVERT(varchar(10), S.freq_subday_interval)
+ ' secondes, de ' + AT.start_time
+ ' à ' + AT.end_time
WHEN 4 THEN ', toutes les ' + CONVERT(varchar(10), S.freq_subday_interval) + ' minutes, de '
+ AT.start_time + ', à ' + AT.end_time
WHEN 8 THEN ', toutes les ' + CONVERT(varchar(10), S.freq_subday_interval) + ' heures, de '
+ AT.start_time + ', à ' + AT.end_time
END AS detail_frequence
, CASE
WHEN S.freq_type = 1 THEN 'Le ' + AD.active_start_date + ' à ' + AT.start_time
WHEN S.freq_type < 64 THEN 'Entre le ' + AD.active_start_date +
+ CASE AD.active_end_date
WHEN '31/12/9999' THEN ' - Pas de date de fin'
ELSE ' et le ' + AD.active_end_date
END
END AS plage_date_dates
, CASE C.name WHEN '[Uncategorized (Local)]' THEN 'Pas de catégorie' ELSE C.name END AS nom_categorie_job
, J.description AS job_description
, J.date_created AS date_creation_job
, J.date_modified AS date_derniere_modification_job
FROM dbo.sysssispackages AS P
INNER JOIN dbo.sysssispackagefolders AS F
ON P.folderid = F.folderid
INNER JOIN sys.server_principals AS SP
ON SP.sid = P.ownersid
LEFT JOIN dbo.sysjobs AS J
ON J.name LIKE P.name + '.Subplan%'
LEFT JOIN dbo.sysjobsteps AS JS
ON J.job_id = JS.job_id
LEFT JOIN dbo.sysjobschedules AS JSCH
ON J.job_id = JSCH.job_id
LEFT JOIN dbo.sysschedules AS S
ON JSCH.schedule_id = S.schedule_id
LEFT JOIN dbo.syscategories AS C
ON J.category_id = C.category_id
LEFT JOIN sys.server_principals AS SPJ
ON SPJ.sid = J.owner_sid
OUTER APPLY (
SELECT freq_day + ', '
FROM (
SELECT CASE WHEN S.freq_interval & 1 = 1 THEN 'Dimanche' ELSE '' END AS freq_day
UNION ALL SELECT CASE WHEN S.freq_interval & 2 = 2 THEN 'Lundi' ELSE '' END
UNION ALL SELECT CASE WHEN S.freq_interval & 4 = 4 THEN 'Mardi' ELSE '' END
UNION ALL SELECT CASE WHEN S.freq_interval & 8 = 8 THEN 'Mercredi' ELSE '' END
UNION ALL SELECT CASE WHEN S.freq_interval & 16 = 16 THEN 'Jeudi' ELSE '' END
UNION ALL SELECT CASE WHEN S.freq_interval & 32 = 32 THEN 'Vendredi' ELSE '' END
UNION ALL SELECT CASE WHEN S.freq_interval & 64 = 64 THEN 'Samedi' ELSE '' END
) AS S
WHERE LEN(freq_day) > 0
FOR XML PATH ('')
) AS D (freq_day_concat)
OUTER APPLY (
SELECT STUFF(STUFF(REPLICATE('0', 6 - LEN(S.active_start_time))
+ CAST(S.active_start_time AS varchar(6)), 3, 0, ':'), 6, 0, ':') AS start_time
, STUFF(STUFF(REPLICATE('0', 6 - LEN(S.active_end_time))
+ CAST(S.active_end_time AS varchar(6)), 3, 0, ':'), 6, 0, ':') AS end_time
) AS AT
OUTER APPLY (
SELECT CONVERT(char(10), CAST(CAST(S.active_start_date AS char(8)) AS date), 103) AS active_start_date
, CONVERT(char(10), CAST(CAST(S.active_end_date AS char(8)) AS date), 103) AS active_end_date
) AS AD
OUTER APPLY (
SELECT CAST(NULLIF(JSCH.next_run_date, 0) AS char(8)) + ' '
+ STUFF(STUFF(REPLICATE('0', 6 - LEN(JSCH.next_run_time)) + CAST(JSCH.next_run_time AS char(6)), 3, 0, ':'), 6, 0, ':') AS next_run_date_time
) AS NR
WHERE F.foldername = 'Maintenance Plans' |
