JOBの内容一覧を表示する
ストアドプロシージャ sp_help_jobでは、詳細なjobの一覧をまとめて取得することができない。(jobテーブルの一覧か、引数を指定しての詳細の情報しか取得できない)
EXEC msdb.dbo.sp_help_job; GO
詳細なjobの一覧をまとめて取得するには以下の直接システムテーブルを使って結合して作る
SysJobs、SysSchedule、SysJobSchedules、SysJobSteps
システム表 msdb.dbo.SysSchedulesテーブルのactivate_start_time,activate_end_timeでInteger型でHHMMSS形式でデータが格納されているので、事前にHH:MM:SS形式の文字列に変換するIntegerToHHMMSSのストアドファンクションを登録しておく
もしくは、IntegerToHHMMSSの部分を上記の内容に置き換える。
SQL Server 2008用
SELECT CASE WHEN st.step_id=1 THEN j.name ELSE '' END as job_name, CASE WHEN st.step_id=1 THEN j.description ELSE '' END as job_description, CASE WHEN st.step_id=1 THEN CASE WHEN j.enabled=1 THEN N'有効' ELSE N'無効'END ELSE '' END as job_enabled, CASE WHEN st.step_id=1 THEN s.name ELSE '' END as schadule_name, CASE WHEN st.step_id=1 THEN CASE WHEN s.enabled=1 THEN N'有効' ELSE N'無効' END ELSE '' END as schedule_enabled , CASE WHEN st.step_id=1 THEN CASE s.freq_type WHEN 1 THEN N'1回のみ実行' WHEN 4 THEN N'毎日(' + STR(s.freq_interval) + N'日ごと)' WHEN 8 THEN N'毎週' + CASE s.freq_interval WHEN 1 THEN N'日' WHEN 2 THEN N'月' WHEN 4 THEN N'火' WHEN 8 THEN N'水' WHEN 16 THEN N'木' WHEN 32 THEN N'金' WHEN 64 THEN N'土' END + N'曜日' WHEN 16 THEN N'毎月(' + STR(s.freq_interval) + N'日)' WHEN 32 THEN N'毎月' + CASE s.freq_interval WHEN 1 THEN N'日' WHEN 2 THEN N'月' WHEN 3 THEN N'火' WHEN 4 THEN N'水' WHEN 5 THEN N'木' WHEN 6 THEN N'金' WHEN 7 THEN N'土' WHEN 8 THEN N'毎' WHEN 9 THEN N'平' WHEN 10 THEN N'土' END + CASE WHEN s.freq_interval<8 THEN N'曜日' ELSE N'日' END WHEN 64 THEN N'SQL Server エージェント サービスが開始されたときに実行' WHEN 128 THEN N'コンピューターがアイドル状態のときに実行' END ELSE '' END as freq_type , CASE WHEN st.step_id=1 THEN CASE WHEN s.freq_subday_type=1 THEN dbo.IntegerToHHMMSS(s.active_start_time) ELSE STR(s.freq_subday_interval) + CASE s.freq_subday_type WHEN 2 THEN N'秒' WHEN 4 THEN N'分' WHEN 8 THEN N'時間' END + 'ごと(' + dbo.IntegerToHHMMSS(s.active_start_time) + '〜' + dbo.IntegerToHHMMSS(s.active_end_time ) + ')' END ELSE '' END as freq_subday_type , st.step_id , st.step_name , st.subsystem , st.command , st.database_name FROM msdb.dbo.SysJobs j JOIN msdb.dbo.SysJobSchedules js ON j.job_id = js.job_id JOIN msdb.dbo.SysSchedules s ON js.schedule_id = s.schedule_id JOIN msdb.dbo.SysJobSteps st ON j.job_id = st.job_id ; GO
SQL Server 2012用
SQL Server 2012であれば、IIFとCHOOSEを使ってもっとコンパクトに書ける。
1,2,4,8,16....となっているところは、MS SQL Serverでビット幅取得で予めストアドファンクション dbo.BitWidthを登録しておく、もしくは、ROUND(LOG(X)/LOG(2),0,1)+1に置き換えて対応する。
SELECT IIF(st.step_id=1,j.name ,'') as job_name , IIF(st.step_id=1,j.description ,'') as job_description , IIF(st.step_id=1,IIF(j.enabled=1,N'有効',N'無効'),'') as job_enabled , IIF(st.step_id=1,s.name ,'') as schadule_name , IIF(st.step_id=1,IIF(s.enabled=1,N'有効',N'無効'),'') as schedule_enabled , IIF(st.step_id=1, CHOOSE(dbo.BitWidth(s.freq_type), N'1回のみ実行', -- 1 N'', -- 2 N'毎日(' + STR(s.freq_interval) + N'日ごと)', -- 4 N'毎週' + CHOOSE(dbo.BitWidth(s.freq_interval), N'日',N'月',N'火',N'水',N'木',N'金',N'土')+'曜日', -- 8 N'毎月(' + STR(s.freq_interval) + N'日)', -- 16 N'毎月' + CHOOSE(s.freq_interval, N'日',N'月',N'火',N'水',N'木',N'金',N'土',N'毎',N'平',N'土') + IIF(s.freq_interval<8,N'曜日',N'日'), -- 32 N'SQL Server エージェント サービスが開始されたときに実行', -- 64 N'コンピューターがアイドル状態のときに実行' -- 128 ) ,'') as freq_type , IIF(st.step_id=1, IIF(s.freq_subday_type=1, dbo.IntegerToHHMMSS(s.active_start_time), STR(s.freq_subday_interval) + CHOOSE(dbo.BitWidth(s.freq_subday_type-1),N'秒',N'分',N'時間') + N'ごと(' + dbo.IntegerToHHMMSS(s.active_start_time) + '〜' + dbo.IntegerToHHMMSS(s.active_end_time ) + ')' ) ,'') as freq_subday_type , st.step_id , st.step_name , st.subsystem , st.command , st.database_name FROM msdb.dbo.SysJobs j JOIN msdb.dbo.SysJobSchedules js ON j.job_id = js.job_id JOIN msdb.dbo.SysSchedules s ON js.schedule_id = s.schedule_id JOIN msdb.dbo.SysJobSteps st ON j.job_id = st.job_id ; GO
尚、他のデータベースでは見慣れない引用符前(N')のNはNational Languageの意味で、Unicodeであることを指す。なくても動作するが指定しておかないと文字化ける場合がある。 これは、SQL-92 標準で同じようにEscapeを意味するE'がPostgreSQLの新しいバージョンでデフォルトになって一時期混乱したことがある。
