T-SQL筆記54_盤查某SP是掛在哪個JOB_JOB的排程清單查詢

使用系統視圖sysjobs和sysjobsteps來查詢作業和作業步驟的定義，並找到

包含特定SP的作業。

1 2 3 4

SELECT j.name AS [Job Name] FROM msdb.dbo.sysjobs AS j INNER JOIN msdb.dbo.sysjobsteps AS s ON j.job_id = s.job_id WHERE s.command LIKE '%YourStoredProcedureName%' -- 替換為要搜索的存儲過程名稱

使用系統視圖sysjobschedules和sysschedules來查詢作業的排程時間。

1 2 3 4 5 6 7 8 9 10

SELECT j.name AS [Job Name], s.name AS [Schedule Name], js.next_run_date AS [Next Run Date], --js.next_run_time AS [Next Run Time] LEFT(Replicate('0', 6 - Len(js.next_run_time)) + Cast(js.next_run_time AS VARCHAR(6)), 2) + ':' + Substring(Replicate('0', 6 - Len(js.next_run_time)) + Cast(js.next_run_time AS VARCHAR(6)), 3, 2) + ':' + RIGHT(Replicate('0', 6 - Len(js.next_run_time)) + Cast(js.next_run_time AS VARCHAR(6)), 2) AS [Next Run Time] FROM msdb.dbo.sysjobschedules AS js INNER JOIN msdb.dbo.sysschedules AS s ON js.schedule_id = s.schedule_id INNER JOIN msdb.dbo.sysjobs AS j ON js.job_id = j.job_id

使用sp_help_job系統存儲過程來查詢作業的定義

EXEC sp_help_job @job_name = 'YourJobName' -- 替換為要搜索的作業名稱

https://learn.microsoft.com/zh-tw/sql/relational-databases/system-stored-procedures/sp-help-job-transact-sql?view=sql-server-ver16

https://byronhu.wordpress.com/2010/12/28/%E6%9F%A5%E8%A9%A2-sql-job-%E5%9F%B7%E8%A1%8C%E7%9A%84%E7%8B%80%E6%B3%81/

使用sp_help_jobschedule系統存儲過程來查詢作業的排程時間。

EXEC sp_help_jobschedule @job_name = 'YourJobName' -- 替換為要查詢的作業名稱