CDC watchdog for databases in AlwaysOn AG

USE [master]
GO
-- =============================================
-- Author:Emil Bialobrzeski
-- Create date: 16/09/2016
-- Description:Procedure is looking for all databases that are 
--part of an availibility group.
--If the group has a PRIMARY role and the database has cdc enabled
--procedure will check if there is a CDC job already created for this database.
--If the job does not exist, procedure will create one.
--If the job exists, procedure will check in what state the job is.
--If the job is running procedure will do a RETURN and nothing will happen.
--IF the job is not running procedure will start it.
-- =============================================
ALTER PROCEDURE [dbo].[sp_cdc_hadr_watchdog]
AS
BEGIN

SET NOCOUNT ON

DECLARE @database_name NVARCHAR(255)
DECLARE @SQL NVARCHAR(MAX)

DECLARE cdc_watchdog_dbs CURSOR FAST_FORWARD READ_ONLY 
FOR 
SELECT  D.name
FROM    sys.dm_hadr_availability_replica_states AS A
        JOIN sys.availability_replicas AS B ON B.replica_id = A.replica_id
        JOIN sys.availability_groups AS AG ON AG.group_id = A.group_id
        JOIN sys.availability_databases_cluster AS ADC ON ADC.group_id = A.group_id
JOIN sys.databases AS D ON D.name = ADC.database_name AND D.is_cdc_enabled = 1
WHERE B.replica_server_name = @@SERVERNAME AND A.role_desc = 'PRIMARY'

OPEN cdc_watchdog_dbs

FETCH NEXT FROM cdc_watchdog_dbs INTO @database_name

WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQL = '
USE ['+@database_name+'];

IF OBJECT_ID(''tempdb..#xp_results'') IS NOT NULL
EXEC (''DROP TABLE #xp_results'')

IF OBJECT_ID(''tempdb..#CDCJobs'') IS NOT NULL
EXEC (''DROP TABLE #CDCJobs'')

CREATE TABLE #CDCJobs(job_id UNIQUEIDENTIFIER
                    , job_type CHAR(8)
, job_name NVARCHAR(255)
, maxtrans SMALLINT
, maxscans SMALLINT
, continuous BIT
, pollinginterval SMALLINT
, retention SMALLINT
, threshold SMALLINT)

INSERT INTO #CDCJobs
EXEC sys.sp_cdc_help_jobs

DECLARE @job_id_captureUNIQUEIDENTIFIER
DECLARE @job_owner_capturesysname

DECLARE @job_id_cleanupUNIQUEIDENTIFIER

DECLARE @is_running INT

SELECT @job_owner_capture = SP.name, @job_id_capture = S.job_id
FROM #CDCJobs AS CJ
JOIN msdb.dbo.sysjobs AS S ON s.name = CJ.job_name
JOIN sys.server_principals AS SP ON SP.sid = S.owner_sid
WHERE CJ.job_type = ''capture''

SELECT @job_id_cleanup = CJ.job_id
FROM #CDCJobs AS CJ
WHERE CJ.job_type = ''cleanup''

IF @job_id_capture IS NULL
EXEC sys.sp_cdc_add_job ''capture''

IF @job_id_cleanup IS NULL
EXEC sys.sp_cdc_add_job ''cleanup''

IF @job_id_capture IS NOT NULL
BEGIN
CREATE TABLE #xp_results (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,
request_source        INT              NOT NULL,
request_source_id     sysname          COLLATE database_default NULL,
running               INT              NOT NULL,
current_step          INT              NOT NULL,
current_retry_attempt INT              NOT NULL,
job_state             INT              NOT NULL)

INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs  0, @job_owner_capture, @job_id_capture 

SELECT @is_running = XR.running
FROM #xp_results AS XR

IF @is_running = 1
RETURN
ELSE
EXEC msdb.dbo.sp_start_job @job_id = @job_id_capture

END'

EXEC (@SQL)

    FETCH NEXT FROM cdc_watchdog_dbs INTO @database_name
END

CLOSE cdc_watchdog_dbs
DEALLOCATE cdc_watchdog_dbs

END
GO