CREATE PROC USP_CHECK_JOB_STATUS
@job_name sysname AS
SET NOCOUNT ON
DECLARE @is_sysadmin INT
DECLARE @job_owner sysname
DECLARE @job_id uniqueidentifier
IF OBJECT_ID('TEMPDB..#job_execution_state') IS NOT NULL
DROP TABLE #job_execution_state
CREATE TABLE #job_execution_state
(
job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)
SELECT @job_owner = SUSER_SNAME()
SELECT @job_id = job_id FROM msdb.dbo.sysjobs where name = @job_name
IF @job_id IS NOT NULL
BEGIN
INSERT INTO #job_execution_state
EXEC master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner, @job_id
IF EXISTS (SELECT * FROM #job_execution_state)
SELECT @job_name AS Name, running AS Status, CASE WHEN running = 1 THEN '실행중' ELSE '실행중이지 않음' END AS Comments
FROM #job_execution_state
ELSE
SELECT @job_name as Name, -1 as Status, '해당 작업이 없습니다.' AS Comments
END
ELSE
SELECT @job_name as Name, -1 as Status, '해당 작업이 없습니다.' AS Comments
SET NOCOUNT OFF
GO
USP_CHECK_JOB_STATUS 'TEST'
/*
Name Status Comments
-------- ----------- --------
TEST 1 실행중
*/
USP_CHECK_JOB_STATUS 'TEST'
/*
Name Status Comments
-------- ----------- --------
TEST 0 실행중이지 않음
*/
USP_CHECK_JOB_STATUS 'TEST1'
/*
Name Status Comments
-------- ----------- --------
TEST1 -1 해당 작업이 없습니다.
*/
