DROP TABLE #MONITOR
GO
create table #MONITOR
(TABLE_NAME varchar(100),
LAST_INSERT_DATE datetime2,
NO_RECORDS decimal,
NO_RECORDS_ORIGIN decimal
)
go


declare @table varchar(100)
declare @date datetime2
declare @query nvarchar(1000)
declare @query2 nvarchar(1000)
declare @no_records decimal
declare @no_records2 decimal
declare @ParamDefinition nvarchar(100)
declare @ParamDefinition2 nvarchar(100)
SET @ParamDefinition = N'@NO_ROWS_OUT decimal OUTPUT';
SET @ParamDefinition2 = N'@NO_ROWS_ORIGIN decimal OUTPUT';

DECLARE db_cursor CURSOR FOR
select TABLE_NAME, max(LAST_INSERT_DATE), 'select @NO_ROWS_OUT = COUNT(*) from '+TABLE_NAME, 'select @NO_ROWS_ORIGIN = count(*)  from ' + f.[SCHEMA]+'.'+f.SOURCE_TABLE
from [ODS_AFCINDRA01].[dbo].D_METTABLE t
inner join D_METJOB j
on t.IDJOB = j.ID
inner join D_METFROM f
on f.IDTABLE = t.ID
where t.IDJOB > 0
and t.FLAG_EXEC = 1
and j.FLAG_EXEC = 1
and t.TABLE_NAME not like 'TMP_%'
group by t.TABLE_NAME, f.SOURCE_TABLE, f.[SCHEMA]
order by TABLE_NAME

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @table, @date, @query, @query2


WHILE @@FETCH_STATUS = 0
BEGIN
	  execute sp_executesql @query, @ParamDefinition, @NO_ROWS_OUT = @no_records OUTPUT;
	  execute sp_executesql @query2, @ParamDefinition2, @NO_ROWS_ORIGIN = @no_records2 OUTPUT;

	insert into #MONITOR(TABLE_NAME, LAST_INSERT_DATE, NO_RECORDS, NO_RECORDS_ORIGIN) select @table, @date, @no_records, @no_records2

	FETCH NEXT FROM db_cursor INTO @table, @date, @query, @query2
END

CLOSE db_cursor
DEALLOCATE db_cursor



select TABLE_NAME, MAX(LAST_INSERT_DATE) LAST_INSERT_DATE, MAX(NO_RECORDS) ODS_RECORDS, SUM(NO_RECORDS_ORIGIN) OPE_RECORDS
from #MONITOR
group by TABLE_NAME


select f.[SCHEMA]+'.'+f.SOURCE_TABLE
from D_METTABLE t
inner join D_METFROM f
on t.ID = f.IDTABLE
where t.IDJOB > 0