Page History

SETEXEC foreign_key_checks = 0;
delete sp, spr, spd, sac, sad, sa
from SHKProcesses sp
join SHKProcessRequesters spr on spr.Id = sp.ID
left join SHKProcessData spd on spd.Process = sp.oid
left join SHKActivities sacsp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

delete from SHKAssignmentsTable as sa
left join SHKProcesses as sp on sa.ActivityProcessId = sp.ID
where (sp.State = 1000006 or sp.State = 1000008 or sp.State = 1000010); 

delete from SHKActivityData as sad
left join SHKActivities as sac on sad.Activity = sac.oid
left join SHKProcesses as sp on sac.ProcessId = sp.ID
where (sp.State = 1000006 or sp.State = 1000008 or sp.State = 1000010); 

delete from SHKActivities as sac
left join SHKProcesses as sp on sac.ProcessId = sp.ID
where (sp.State = 1000006 or sp.State = 1000008 or sp.State = 1000010); 

delete from SHKProcessData as spd
left join SHKProcesses SHKActivityDataas sadsp on sadspd.ActivityProcess = sacsp.oid
where (sp.State = 1000006 or sp.State = 1000008 or sp.State = 1000010); 

delete from SHKProcessRequesters as spr
left join SHKProcesses SHKAssignmentsTableas sasp on saspr.ActivityProcessIdId = sp.ID
where (sp.State = 1000006 or sp.State = 1000008 or sp.State = 1000010);
SET foreign_key_checks = 1; 

delete from SHKProcesses where (State = 1000006 or State = 1000008 or State = 1000010); 
 
EXEC sp_MSforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

delete from SHKXPDLData as xd
left join SHKXPDLS as on x.oid= xd.XPDL
where concat(x.XPDLId, x.XPDLVersion) not in (
    select concat(packageId, xd from SHKXPDLS x join SHKXPDLData xd on x.oid= xd.XPDLpackageVersion) as id from (
        select def.PackageId as packageId, def.ProcessDefinitionVersion as packageVersion 
        from SHKActivities act 
        join SHKProcesses pro on act.Process = pro.oid 
        left join SHKProcessDefinitions def on pro.ProcessDefinition = def.oid 
        group by def.PackageId, def.ProcessDefinitionVersion 
        union 
        select packageId, packageVersion from app_package
    ) as used_processes group by packageId, packageVersion
);

delete from SHKXPDLS x 
where concat(x.XPDLId, x.XPDLVersion) not in (
    select concat(packageId,packageVersion) as id from (
        select def.PackageId as packageId, def.ProcessDefinitionVersion as packageVersion 
        from SHKActivities act 
        join SHKProcesses pro on act.Process = pro.oid 
        left join SHKProcessDefinitions def on pro.ProcessDefinition = def.oid 
        group by def.PackageId, def.ProcessDefinitionVersion 
        union 
        select packageId, packageVersion from app_package
    ) as used_processes group by packageId, packageVersion
);

EXEC sp_MSforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"