This Stored Proc will check all satellite tables (prefix s_) and see if they have any zero hub keys (prefix h_).
This should not happen if the order of the jobs is correct when building the data vault.
The code makes use of custom Red parameters, in this case we named it email_to_addr_on_updates. Enter the emails you want the procedure to contact after it runs.
CREATE PROCEDURE c_dm_row_check_email
@p_sequence integer
, @p_job_name varchar(256)
, @p_task_name varchar(256)
, @p_job_id integer
, @p_task_id integer
, @p_return_msg varchar(256) OUTPUT
, @p_status integer OUTPUT
AS
SET XACT_ABORT OFF -- Turn off auto abort on errors
SET NOCOUNT ON -- Turn off row count messages
--===============================================================
-- Control variables used in most programs
--===============================================================
DECLARE
@v_msgtext varchar(256) -- Text for audit_trail
, @v_step integer -- step number
, @v_return_status integer -- Query result status
, @v_db_code varchar(10) -- Database error code
, @v_db_msg varchar(100) -- Database error message
DECLARE
@v_name_of_job_to_release VARCHAR(256)
, @v_Run_next_job varchar(10)
, @v_return_code VARCHAR(1)
, @v_return_msg VARCHAR(256)
, @v_job_name VARCHAR(256)
, @v_job_name_temp VARCHAR(256)
, @v_result INT
, @email_to VARCHAR(512)
--===============================================================
-- Main
--===============================================================
BEGIN TRY
SELECT @email_to = dss_parameter_value
FROM dss_parameter WHERE dss_parameter_name = 'email_to_addr_on_updates'
SET @v_job_name = substring(@p_job_name,9,3)
--SET @v_job_name_temp = 'edw_300_fssacr_dim'
--SET @v_job_name = substring(@v_job_name_temp,9,3)
--select @v_job_name
DECLARE @bodyMsg nvarchar(max)
DECLARE @subject nvarchar(max)
DECLARE @tableHTML nvarchar(max)
DECLARE @Table NVARCHAR(MAX) = N''
SET @subject = 'Data Mart Check for ' + upper(@v_job_name)
SELECT @Table = @Table +'<tr style ="font-size: 12px;font-weight:normal;">' +
case when [rowcounts]=0 then '<td bgcolor="yellow">' else '<td>' end + [tablename]+ '</td>' +
case when [rowcounts]=0 then '<td bgcolor="yellow">' else '<td>' end + cast([rowcounts] as nvarchar(30))+ '</td>' +
'</tr>'
FROM(SELECT
t.NAME as tablename,
p.rows as rowcounts,
getdate() as countdate
FROM
sys.tables t
INNER JOIN
sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
WHERE t.is_ms_shipped = 0
AND (left(t.NAME,6) = 'ft_'+ @v_job_name or left(t.NAME,7) = 'dim_'+@v_job_name)
AND substring(t.NAME,len(t.NAME)-3,4) <> '_old'
GROUP BY
p.Rows, t.NAME, s.Name)dataset
SET @tableHTML =
N'<H3><font color="Black">Row Counts</H3>' +
N'<table border="1" align="center" cellpadding="2" cellspacing="0" style="color:black;font-family:arial,helvetica,sans-serif;text-align:left;" >' +
N'<tr style ="font-size: 12px;font-weight: normal;background: #61c6c4;">
<th>Table</th>
<th>Rowcount</th>
</tr>' + @Table + N'</table>'
--select @tableHTML
EXEC msdb.dbo.sp_send_dbmail @recipients = @email_to,
@subject = @subject,
@body = @tableHTML,
@body_format = 'HTML' ;
--EXEC msdb.dbo.sp_send_dbmail @recipients='[email protected]',
SET @v_step = 300;
SET @p_return_msg = 'c_dm_row_check_email completed.';
SET @p_status = 1;
END TRY
BEGIN CATCH
SET @v_DB_code = CAST(/**/ERROR_NUMBER() AS VARCHAR);
SET @v_DB_msg = /**/ERROR_MESSAGE();
SET @p_status =/**/-3;
SET @p_return_msg = LEFT(
'c_dm_row_check_email FAILED' +
'. Step num.: ' + CAST(@v_step AS VARCHAR) +
'. Err. num.: ' + @v_DB_code +
'. Err. msg.: ''' + @v_DB_msg + '''',
256
);
EXEC @v_return_status = WSWrkError
'E',
@p_job_name,
@p_task_name,
@p_sequence,
@p_return_msg,
@v_DB_code,
@v_DB_msg,
@p_task_ID,
@p_job_ID,
NULL;
END CATCH;
RETURN 0