USE [iQSonarScan]
GO

/****** Object:  StoredProcedure [dbo].[iprange_de]  Script Date: 22/09/2016 17:41:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE PROCEDURE [dbo].[iprange_de]
(
	@Iprange				INT
)
AS
BEGIN

IF OBJECT_ID('tempdb..#DeviceToRemove') IS NOT NULL
DROP TABLE #DeviceToRemove
CREATE TABLE #DeviceToRemove (DeviceID int)

IF OBJECT_ID('tempdb..#FoundDeviceToRemove') IS NOT NULL
DROP TABLE #FoundDeviceToRemove
CREATE TABLE #FoundDeviceToRemove (FoundDeviceID int)

IF OBJECT_ID('tempdb..#ApplicationToRemove') IS NOT NULL
DROP TABLE #ApplicationToRemove
CREATE TABLE #ApplicationToRemove (ApplicationID int)

IF OBJECT_ID('tempdb..#FoundApplicationToRemove') IS NOT NULL
DROP TABLE #FoundApplicationToRemove
CREATE TABLE #FoundApplicationToRemove (FoundApplicationID int)

IF OBJECT_ID('tempdb..#EsxDatasoureToRemove') IS NOT NULL
DROP TABLE #EsxDatasoureToRemove
CREATE TABLE #EsxDatasoureToRemove (EsxDatastoreID int)

IF OBJECT_ID('tempdb..#ClusterToRemove') IS NOT NULL
DROP TABLE #ClusterToRemove
CREATE TABLE #ClusterToRemove (MSClusterNetworkID int)


INSERT INTO #DeviceToRemove
select ip.DeviceID from IPAddress ip
JOIN IPRange ipr
on (ip.IPAddressBinary >= ipr.StartIPBinary 
and ip.IPAddressBinary <= ipr.EndIPBinary)
where ip.IsVerified =1 and ipr.IPRangeID = @Iprange

INSERT INTO #ApplicationToRemove
SELECT A.ApplicationID FROM Application A 
JOIN MapApplicationDevice MAD 
ON MAD.ApplicationID =A.ApplicationID
JOIN Device D 
ON D.DeviceID =MAD.DeviceID 
WHERE MAD.DeviceID IN(SELECT Deviceid FROM #DeviceToRemove)


INSERT INTO #FoundDeviceToRemove
select fd.FoundDeviceID from iprange ipr
left join founddevice fd on fd.hostname like '[1-9]%.%.%.%' and
fd.hostname not like '%[a-z]%' and
dbo.convertiptobinary(fd.hostname) >= ipr.startipbinary and 
 dbo.convertiptobinary(fd.hostname) <= ipr.endipbinary
 where ipr.IPRangeID = @Iprange


 INSERT INTO #FoundDeviceToRemove
 SELECT FoundDeviceID from FoundDevice where DeviceID in(
 select DeviceID from #DeviceToRemove)


 INSERT INTO #FoundApplicationToRemove
select foundapplicationid from FoundApplication fa
join #DeviceToRemove dtr ON dtr.DeviceID = fa.DeviceID


INSERT INTO #ClusterToRemove
SELECT DISTINCT MSClusterNetworkID FROM MSClusterNetwork mcn
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = mcn.ApplicationID)


INSERT INTO #EsxDatasoureToRemove
SELECT DISTINCT ed.EsxDatastoreID FROM EsxDatastore ed
LEFT OUTER JOIN MapApplicationEsxDatastore maed ON maed.EsxDatastoreID = ed.EsxDatastoreID
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = maed.ApplicationID)

DELETE fdshd FROM FoundDeviceScanHistoryDebug fdshd
JOIN FoundDeviceScanHistory fdsh ON fdsh.FoundDeviceScanHistoryID = fdshd.FoundDeviceScanHistoryID
WHERE EXISTS (SELECT 1 FROM #FoundDeviceToRemove fdtr WHERE fdtr.FoundDeviceID = fdsh.FoundDeviceID)

DELETE fdsh FROM FoundDeviceScanHistory fdsh
WHERE EXISTS (SELECT 1 FROM #FoundDeviceToRemove fdtr WHERE fdtr.FoundDeviceID = fdsh.FoundDeviceID)

update FoundDevice set DeviceID = NULL 
WHERE DeviceID in(select DeviceID from #DeviceToRemove)


DELETE maed FROM MapApplicationEsxDatastore maed
WHERE EXISTS (SELECT 1 FROM #EsxDatasoureToRemove edtr WHERE edtr.EsxDatastoreID = maed.EsxDatastoreID)

DELETE ed FROM EsxDatastore ed
WHERE EXISTS (SELECT 1 FROM #EsxDatasoureToRemove edtr WHERE edtr.EsxDatastoreID = ed.EsxDatastoreID)

--

DELETE fash FROM FoundApplicationScanHistory fash
WHERE EXISTS (SELECT 1 FROM #FoundApplicationToRemove fatr WHERE fatr.FoundApplicationID = fash.FoundApplicationID)

DELETE fach FROM FoundApplicationCredentialHistory fach
WHERE EXISTS (SELECT 1 FROM #FoundApplicationToRemove fatr WHERE fatr.FoundApplicationID = fach.FoundApplicationID)

DELETE at FROM ApplicationTroubleshooting at
WHERE EXISTS (SELECT 1 FROM #FoundApplicationToRemove fatr WHERE fatr.FoundApplicationID = at.FoundApplicationID)

DELETE manfa FROM MapApplicationNodeFoundApplication manfa
WHERE EXISTS (SELECT 1 FROM #FoundApplicationToRemove fatr WHERE fatr.FoundApplicationID = manfa.FoundApplicationID)

DELETE faaj FROM FoundApplicationActiveJob faaj
WHERE EXISTS (SELECT 1 FROM #FoundApplicationToRemove fatr WHERE fatr.FoundApplicationID = faaj.FoundApplicationID)

DELETE fa FROM FoundApplication fa
WHERE EXISTS (SELECT 1 FROM #FoundApplicationToRemove fatr WHERE fatr.FoundApplicationID = fa.FoundApplicationID)

--

DELETE ash FROM ApplicationScanHistory ash
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = ash.ApplicationID)


DELETE mcip4 FROM MSClusterIPv4Address mcip4
WHERE EXISTS (SELECT 1 FROM #ClusterToRemove ctr WHERE ctr.MSClusterNetworkID = mcip4.MSClusterNetworkID)

DELETE mcip6 FROM MSClusterIPv6Address mcip6
WHERE EXISTS (SELECT 1 FROM #ClusterToRemove ctr WHERE ctr.MSClusterNetworkID = mcip6.MSClusterNetworkID)



DELETE FROM MSClusterIPv4Address WHERE MSClusterIPv4AddressID IN(
SELECT MS4.MSClusterIPv4AddressID FROM MSClusterNetworkInterface MS
JOIN MSClusterIPv4Address  MS4
ON MS4.MSClusterNetworkInterfaceID =MS.MSClusterNetworkInterfaceID
WHERE MS.MSClusterNetworkInterfaceID IN(SELECT MSClusterNetworkInterfaceID FROM MSClusterNetworkInterface mcni
WHERE EXISTS (SELECT 1 FROM #ClusterToRemove ctr WHERE ctr.MSClusterNetworkID = mcni.MSClusterNetworkID)))

DELETE FROM MSClusterIPv6Address WHERE MSClusterIPv6AddressID IN(
SELECT MS4.MSClusterIPv6AddressID FROM MSClusterNetworkInterface MS
JOIN MSClusterIPv6Address  MS4
ON MS4.MSClusterNetworkInterfaceID =MS.MSClusterNetworkInterfaceID
WHERE MS.MSClusterNetworkInterfaceID IN(SELECT MSClusterNetworkInterfaceID FROM MSClusterNetworkInterface mcni
WHERE EXISTS (SELECT 1 FROM #ClusterToRemove ctr WHERE ctr.MSClusterNetworkID = mcni.MSClusterNetworkID)))



DELETE mcni FROM MSClusterNetworkInterface mcni
WHERE EXISTS (SELECT 1 FROM #ClusterToRemove ctr WHERE ctr.MSClusterNetworkID = mcni.MSClusterNetworkID)


DELETE mcn FROM MSClusterNetwork mcn
WHERE EXISTS (SELECT 1 FROM #ClusterToRemove ctr WHERE ctr.MSClusterNetworkID = mcn.MSClusterNetworkID)

DELETE mcr FROM MSClusterResource mcr
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = mcr.ApplicationID)

DELETE mcrt FROM MSClusterResourceType mcrt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = mcrt.ApplicationID)

DELETE mcrg FROM MSClusterResourceGroup mcrg
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = mcrg.ApplicationID)



DELETE mcs FROM MSClusterService mcs
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = mcs.ApplicationID)

DELETE mcdp FROM MSClusterDiskPartition mcdp
JOIN MSClusterDisk mcd ON mcd.MSClusterDiskID = mcdp.MSClusterDiskID
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = mcd.ApplicationID)

DELETE mc FROM MSClusterDisk mc
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = mc.ApplicationID)

DELETE mc FROM MSClusterSharedVolume mc
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = mc.ApplicationID)

DELETE mcn FROM MSClusterNode mcn
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = mcn.ApplicationID)

DELETE mc FROM MSCluster mc
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = mc.ApplicationID)

DELETE m FROM MSSQLCurrentUsers m
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = m.ApplicationID)

DELETE m FROM MSSQLLogin m
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = m.ApplicationID)

DELETE m FROM MSSQLSession m
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = m.ApplicationID)

DELETE m FROM MSSQLHA m
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = m.ApplicationID)

DELETE m FROM MSSQLLicense m
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = m.ApplicationID)

DELETE m FROM MSSQLDatabase m
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = m.ApplicationID)

DELETE opt FROM OracleActiveDataGuard opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleActiveDataGuardFastIncrementalBackup opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleAdminLicense opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleAWSSegment opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleAWS opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleComponents opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleCounts opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleCPUUsageStatistics opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleDBACubes opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleDBAFeatureUsageStatistics opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleDBAFlashbackArchiveTables opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleDBAFlashbackArchive opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleDBALOBAdvancedCompression opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleDBAMiningModels opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleDBARecycleBin opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleDbaRegistry opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleDBATableAdvancedCompression opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleDBAUsers opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleDBGridCloudControl opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleEMPacks opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleEncryptedColumn opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleEncryptedSessions opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleEncryptedTablespace opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleFeatureUsageStatistics opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE op FROM OraclegvParameter op
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = op.ApplicationID)

DELETE opt FROM OraclegvInstance opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OraclegvParameter opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleiASComponent opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleiASInstalledApplication opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleiASOPatch opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleiASOption opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleiAS opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleManagedTargets opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleManagmentFeatures opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleManagmentPack opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleMgmtAdminLicenses opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleMgmtLicenseConfirmation opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleMgmtLicenses opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleMgmtTargets opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE aia FROM ApplicationIPAddress aia
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = aia.ApplicationID)

DELETE opt FROM OracleMultitenantContainers opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleOEMControl opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleOlapCubes opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleOptionUsage opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleOWB opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OraclePartitionedTables opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OraclePatches opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OraclePrivilege opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleRolePrivs opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleRunningProgram opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleSecureFile opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleSessionCount opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleSpatial opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleSQLProfiles opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleTuningAdvisor opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleTuningSets opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleTuningSetsRef opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleTuxedo opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OracleUserSysPrivs opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OraclevDatabase opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OraclevInstance opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OraclevLicense opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OraclevOption opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OraclevParameter opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OraclevSession opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OraclevSessionConnectInfo opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE opt FROM OraclevVersion opt
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = opt.ApplicationID)

DELETE w FROM WebLogic w
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = w.ApplicationID)

DELETE w FROM WebLogicComponent w
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = w.ApplicationID)

DELETE w FROM WeblogicPort w
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = w.ApplicationID)

DELETE ach FROM ApplicationCredentialHistory ach
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = ach.ApplicationID)

DELETE FROM ApplicationDeployedSoftware WHERE ApplicationNodeid IN(
SELECT ApplicationNodeID  FROM ApplicationNode WHERE Deviceid IN(
SELECT DeviceID FROM #DeviceToRemove  ))

DELETE FROM MapApplicationNodeFoundDevice WHERE FoundDeviceid IN(
SELECT FoundDeviceID FROM #FoundDeviceToRemove)

DELETE manfd FROM MapApplicationNodeFoundDevice manfd
JOIN ApplicationNode an ON an.ApplicationNodeID = manfd.ApplicationNodeID
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = an.ApplicationID OR atr.ApplicationID = an.HostedApplicationID)
OR EXISTS (SELECT 1 FROM #FoundDeviceToRemove fdtr WHERE fdtr.FoundDeviceID = manfd.FoundDeviceID)

DELETE manfa FROM MapApplicationNodeFoundApplication manfa
JOIN ApplicationNode an ON an.ApplicationNodeID = manfa.ApplicationNodeID
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = an.ApplicationID OR atr.ApplicationID = an.HostedApplicationID)


DELETE  FROM ApplicationNode WHERE Deviceid IN(
SELECT DeviceID FROM #DeviceToRemove  )





DELETE an FROM ApplicationNode an
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = an.ApplicationID OR atr.ApplicationID = an.HostedApplicationID)

DELETE at FROM ApplicationTroubleshooting at
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = at.ApplicationID)

DELETE ad FROM ApplicationDatabase ad
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = ad.ApplicationID)

DELETE ash FROM ApplicationScriptHistory ash
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = ash.ApplicationID)

DELETE ae FROM ApplicationESX ae
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = ae.ApplicationID)

DELETE aaj FROM ApplicationActiveJob aaj
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = aaj.ApplicationID)

DELETE vd FROM VirtualDevice vd
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = vd.HostApplicationID)

DELETE mad FROM MapApplicationDevice mad
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = mad.ApplicationID)
OR EXISTS (SELECT 1 FROM #DeviceToRemove dtr WHERE dtr.DeviceID = mad.DeviceID)

DELETE ads FROM ApplicationDeployedSoftware ads
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = ads.ApplicationID)

DELETE manfa FROM MapApplicationNodeFoundApplication manfa
JOIN ApplicationNode an ON an.ApplicationNodeID = manfa.ApplicationNodeID
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = an.ApplicationID)

DELETE an FROM ApplicationNode an
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = an.ApplicationID)

DELETE mstsl FROM MSTerminalServerLicense mstsl
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = mstsl.ApplicationID)

DELETE fach FROM FoundApplicationCredentialHistory fach
JOIN FoundApplication fa ON fa.FoundApplicationID = fach.FoundApplicationID
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = fa.ApplicationID)

DELETE fashd FROM FoundApplicationScanHistoryDebug fashd
JOIN FoundApplicationScanHistory fash ON fash.FoundApplicationScanHistoryID = fashd.FoundApplicationScanHistoryID
JOIN FoundApplication fa ON fa.FoundApplicationID = fash.FoundApplicationID
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = fa.ApplicationID)


DELETE fash FROM FoundApplicationScanHistory fash
JOIN FoundApplication fa ON fa.FoundApplicationID = fash.FoundApplicationID
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = fa.ApplicationID)

DELETE manfa FROM MapApplicationNodeFoundApplication manfa
JOIN FoundApplication fa ON fa.FoundApplicationID = manfa.FoundApplicationID
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = fa.ApplicationID)


DELETE fa FROM FoundApplication fa
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = fa.ApplicationID)

DELETE ole FROM OraclevLicenseExtra ole
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = ole.ApplicationID)

DELETE oi FROM OraclegvInstance oi
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = oi.ApplicationID)

DELETE odi FROM OracleDBAFeaturesInfo odi
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = odi.ApplicationID)

DELETE a FROM Application a
WHERE EXISTS (SELECT 1 FROM #ApplicationToRemove atr WHERE atr.ApplicationID = a.ApplicationID)




DELETE fdp FROM FoundDevicePort fdp
WHERE EXISTS (SELECT 1 FROM #FoundDeviceToRemove fdtr WHERE fdtr.FoundDeviceID = fdp.FoundDeviceID)

DELETE fdl FROM FoundDeviceLog fdl
WHERE EXISTS (SELECT 1 FROM #FoundDeviceToRemove fdtr WHERE fdtr.FoundDeviceID = fdl.FoundDeviceID)

DELETE fdch FROM FoundDeviceCredentialHistory fdch
WHERE EXISTS (SELECT 1 FROM #FoundDeviceToRemove fdtr WHERE fdtr.FoundDeviceID = fdch.FoundDeviceID)

DELETE fdch FROM FoundDeviceCommandHistory fdch
WHERE EXISTS (SELECT 1 FROM #FoundDeviceToRemove fdtr WHERE fdtr.FoundDeviceID = fdch.FoundDeviceID)

DELETE dt FROM DeviceTroubleshooting dt
WHERE EXISTS (SELECT 1 FROM #FoundDeviceToRemove fdtr WHERE fdtr.FoundDeviceID = dt.FoundDeviceID)

DELETE fd FROM FoundDevice fd
WHERE EXISTS (SELECT 1 FROM #FoundDeviceToRemove fdtr WHERE fdtr.FoundDeviceID = fd.FoundDeviceID)

--

DELETE dk FROM DeviceKeyword dk
WHERE EXISTS (SELECT 1 FROM #DeviceToRemove dtr WHERE dtr.DeviceID = dk.DeviceID)

DELETE da FROM DisplayAdapter da
WHERE EXISTS (SELECT 1 FROM #DeviceToRemove dtr WHERE dtr.DeviceID = da.DeviceID)

DELETE pm FROM PhysicalMemory pm
WHERE EXISTS (SELECT 1 FROM #DeviceToRemove dtr WHERE dtr.DeviceID = pm.DeviceID)

DELETE mds FROM MapDeviceSoftware mds
WHERE EXISTS (SELECT 1 FROM #DeviceToRemove dtr WHERE dtr.DeviceID = mds.DeviceID)

DELETE ip FROM IPAddressPort ip
JOIN IPAddress i ON i.IPAddressID = ip.IPAddressID
WHERE EXISTS (SELECT 1 FROM #DeviceToRemove dtr WHERE dtr.DeviceID = i.DeviceID)

DELETE mdpp FROM MapDeviceProcessPort mdpp
JOIN IPAddress i ON i.IPAddressID = mdpp.IPAddressID
WHERE EXISTS (SELECT 1 FROM #DeviceToRemove dtr WHERE dtr.DeviceID = i.DeviceID)

DELETE i FROM IPAddress i
WHERE EXISTS (SELECT 1 FROM #DeviceToRemove dtr WHERE dtr.DeviceID = i.DeviceID)


DELETE dshd FROM DeviceScanHistoryDebug dshd
JOIN DeviceScanHistory dsh ON dsh.DeviceScanHistoryID = dshd.DeviceScanHistoryID
WHERE EXISTS (SELECT 1 FROM #DeviceToRemove dtr WHERE dtr.DeviceID = dsh.DeviceID)

DELETE mdp FROM MapDeviceProcess mdp
WHERE EXISTS (SELECT 1 FROM #DeviceToRemove dtr WHERE dtr.DeviceID = mdp.DeviceID)

DELETE dsh FROM DeviceScanHistory dsh
WHERE EXISTS (SELECT 1 FROM #DeviceToRemove dtr WHERE dtr.DeviceID = dsh.DeviceID)

DELETE at FROM ApplicationTroubleshooting at
JOIN FoundApplication fa ON fa.FoundApplicationID = at.FoundApplicationID
WHERE EXISTS (SELECT 1 FROM #DeviceToRemove dtr WHERE fa.FoundOnDeviceID = dtr.DeviceID)

DELETE fashd FROM FoundApplicationScanHistoryDebug fashd
JOIN FoundApplicationScanHistory fash ON fash.FoundApplicationScanHistoryID = fashd.FoundApplicationScanHistoryID
JOIN FoundApplication fa ON fa.FoundApplicationID = fash.FoundApplicationID
WHERE EXISTS (SELECT 1 FROM #DeviceToRemove dtr WHERE fa.FoundOnDeviceID = dtr.DeviceID)

DELETE fash FROM FoundApplicationScanHistory fash
JOIN FoundApplication fa ON fa.FoundApplicationID = fash.FoundApplicationID
WHERE EXISTS (SELECT 1 FROM #DeviceToRemove dtr WHERE fa.FoundOnDeviceID = dtr.DeviceID)

DELETE fach FROM FoundApplicationCredentialHistory fach
JOIN FoundApplication fa ON fa.FoundApplicationID = fach.FoundApplicationID
WHERE EXISTS (SELECT 1 FROM #DeviceToRemove dtr WHERE fa.FoundOnDeviceID = dtr.DeviceID)

DELETE fa FROM FoundApplication fa
WHERE EXISTS (SELECT 1 FROM #DeviceToRemove dtr WHERE fa.FoundOnDeviceID = dtr.DeviceID)

DELETE wlc FROM WebLogicComponent wlc
WHERE EXISTS (SELECT 1 FROM #DeviceToRemove dtr WHERE wlc.DeviceID = dtr.DeviceID)

DELETE wlp FROM WeblogicPort wlp
JOIN WebLogic wl ON wl.ApplicationID = wlp.ApplicationID
WHERE EXISTS (SELECT 1 FROM #DeviceToRemove dtr WHERE wl.DeviceID = dtr.DeviceID)


DELETE wl FROM WebLogic wl
WHERE EXISTS (SELECT 1 FROM #DeviceToRemove dtr WHERE wl.DeviceID = dtr.DeviceID)

DELETE at FROM ApplicationTroubleshooting at

DELETE FROM ParseFileMatchGroup WHERE ParseFileMatchID IN(
SELECT ParseFileMatchID FROM ParseFileMatch WHERE ParseFileExpressionID IN(
SELECT ParseFileExpressionID FROM ParseFile 
WHERE Deviceid IN(SELECT DeviceID FROM #DeviceToRemove)))


DELETE FROM ParseFileMatch WHERE ParseFileExpressionID IN(
SELECT ParseFileExpressionID FROM ParseFile 
WHERE Deviceid IN(SELECT DeviceID FROM #DeviceToRemove))

DELETE FROM MapParseFileExpressionFoundParseFile WHERE ParseFileExpressionID IN(
SELECT ParseFileExpressionID FROM ParseFile 
WHERE Deviceid IN(SELECT DeviceID FROM #DeviceToRemove))

delete from FoundParseFileScanHistory WHERE FoundParseFileID  IN(
SELECT FoundParseFileID FROM FoundParseFile 
WHERE Deviceid IN(SELECT DeviceID FROM #DeviceToRemove))

delete from FoundParseFileCredentialHistory where FoundParseFileID in(
select FoundParseFileID from FoundParseFile where DeviceID in(select DeviceID from #DeviceToRemove))



DELETE  FROM  FoundParseFile  where FoundParseFileID in(
SELECT FoundParseFileID FROM FoundParseFile 
WHERE Deviceid IN(SELECT DeviceID FROM #DeviceToRemove))




DELETE FROM ParseFileScanHistory WHERE ParseFileID  IN(
SELECT ParseFileID FROM ParseFile 
WHERE Deviceid IN(SELECT DeviceID FROM #DeviceToRemove))

DELETE FROM ParseFileCredentialHistory WHERE ParseFileID  IN(
SELECT ParseFileID FROM ParseFile 
WHERE Deviceid IN(SELECT DeviceID FROM #DeviceToRemove))

Delete from MapParseFileExpressionParseFile
where ParseFileID in(
select ParseFileID from ParseFile where DeviceID in(select DeviceID from #DeviceToRemove))


DELETE FROM ParseFile WHERE ParseFileID  IN(
SELECT ParseFileID FROM ParseFile 
WHERE Deviceid IN(SELECT DeviceID FROM #DeviceToRemove))




DELETE dk FROM Disk dk
WHERE EXISTS (SELECT 1 FROM #DeviceToRemove dtr WHERE dtr.DeviceID = dk.DeviceID)

DELETE fd FROM FoundDevice fd
WHERE EXISTS (SELECT 1 FROM #DeviceToRemove dtr WHERE dtr.DeviceID = fd.DeviceID)

DELETE du FROM DeviceUser du
WHERE EXISTS (SELECT 1 FROM #DeviceToRemove dtr WHERE dtr.DeviceID = du.DeviceID)

DELETE dcf FROM DeviceConfigFile dcf
WHERE EXISTS (SELECT 1 FROM #DeviceToRemove dtr WHERE dtr.DeviceID = dcf.DeviceID)

DELETE dt FROM DeviceTroubleshooting dt


DELETE dch FROM DeviceCredentialHistory dch
WHERE EXISTS (SELECT 1 FROM #DeviceToRemove dtr WHERE dtr.DeviceID = dch.DeviceID)

DELETE dch FROM DeviceCommandHistory dch
WHERE EXISTS (SELECT 1 FROM #DeviceToRemove dtr WHERE dtr.DeviceID = dch.DeviceID)

DELETE dc FROM DeviceCPU dc
WHERE EXISTS (SELECT 1 FROM #DeviceToRemove dtr WHERE dtr.DeviceID = dc.DeviceID)

DELETE dl FROM DeviceLog dl
WHERE EXISTS (SELECT 1 FROM #DeviceToRemove dtr WHERE dtr.DeviceID = dl.DeviceID)

DELETE vd FROM VirtualDevice vd
WHERE EXISTS (SELECT 1 FROM #DeviceToRemove dtr WHERE dtr.DeviceID = vd.GuestDeviceID)

DELETE FROM MapDeviceProcessPort WHERE MapDeviceProcessID IN(
SELECT MapDeviceProcessID FROM MapDeviceProcess WHERE DeviceID IN( SELECT DeviceID FROM #DeviceToRemove  ))

DELETE FROM MapDeviceProcess 
WHERE DeviceID IN( SELECT DeviceID FROM #DeviceToRemove  )

DELETE FROM MapDeviceService 
WHERE DeviceID IN( SELECT DeviceID FROM #DeviceToRemove  )

UPDATE Device 
SET CollisionDeviceID =NULL WHERE CollisionDeviceID IN(
SELECT DeviceID FROM #DeviceToRemove  )

UPDATE Device 
SET PhysicalDeviceID =NULL WHERE PhysicalDeviceID IN(
SELECT DeviceID FROM #DeviceToRemove  )

DELETE FROM DerivedSoftware 
WHERE DeviceID IN( SELECT DeviceID FROM #DeviceToRemove  )

delete  from SoundController where DeviceID in(
select DeviceID from #DeviceToRemove)

delete from DeviceActiveJob where DeviceID in(
select DeviceID from #DeviceToRemove)

DELETE FROM Device 
WHERE DeviceID IN( SELECT DeviceID FROM #DeviceToRemove  )

--Delete Ip ranges
DELETE FROM MapIPRange_Server
WHERE IPRangeid = @Iprange


DELETE FROM MapIPRangePortList
WHERE IPRangeid = @Iprange

DELETE FROM IPRange
WHERE IPRangeid = @Iprange


END;