-- Intézmény adatokat eltávolító folyamat figyelése
declare @INTEZMENYEK varchar(max) = 'klik031053001'; -- <----- Megmaradó intézmény azonosítók 
declare @SQL nvarchar(max) = '';



set @INTEZMENYEK = stuff((
    select concat(',', i.ID)
    from string_split(@INTEZMENYEK, ',') a
    inner join T_INTEZMENY (nolock) i on i.C_AZONOSITO = a.value
    for xml path('')
), 1, 1, '');

declare @table_list table(table_name sysname, to_do bit);

select @SQL += 'SELECT ''' + s.name + '.' + t.name + ''' AS table_name, ' + 
               'CASE WHEN EXISTS(SELECT * FROM ' + s.name + '.' + t.name + ' (NOLOCK) WHERE ' + c.name + ' NOT IN (' + @INTEZMENYEK + ') ) THEN 1 ELSE 0 END AS ToDo;' + char(13)
from sys.tables t
inner join sys.schemas s on s.schema_id = t.schema_id
inner join sys.columns c on t.object_id = c.object_id
where c.name in ('C_INTEZMENYID', 'C_ALINTEZMENYID')
and s.name not in ('auditlog', 'core', 'dev', 'HangFire')
and t.name not in ('XmlAudit')
order by s.name, t.name;

insert into @table_list
exec(@SQL);

select  distinct 
        spid, 
        datediff(second, last_batch, getdate()) duration_sec,
        p.cmd,
        substring(s.text, p.stmt_start / 2 + 1, p.stmt_end / 2 + 1) command,
        p.status,
        r.percent_complete,
        r.estimated_completion_time / 1000 seconds_remaining
from sys.sysprocesses p
cross apply sys.dm_exec_sql_text(p.sql_handle) s
left join sys.dm_exec_requests r on r.session_id = p.spid
where p.spid <> @@spid
and s.text like '%CHECKPOINT;%'
and p.status in ('running', 'runnable', 'suspended');

select * from @table_list
where to_do > 0;