USE [VIGIL-Analytics]
GO


-- DROP

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_cleanup_data]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_cleanup_data]
GO


-- CREATE

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_cleanup_data]
AS
	SET NOCOUNT ON

	DELETE FROM [dbo].[alarmevents] WHERE vcm_dvr_id NOT IN (SELECT idx FROM [dbo].[DVR])
	DELETE FROM [dbo].[audit_daily_user_summary] WHERE vcm_dvr_id NOT IN (SELECT idx FROM [dbo].[DVR])
	DELETE FROM [dbo].[audit_details] WHERE vcm_dvr_id NOT IN (SELECT idx FROM [dbo].[DVR])
	DELETE FROM [dbo].[audit_details_info] WHERE vcm_dvr_id NOT IN (SELECT idx FROM [dbo].[DVR])
	DELETE FROM [dbo].[audit_details_info_types] WHERE vcm_dvr_id NOT IN (SELECT idx FROM [dbo].[DVR])
	DELETE FROM [dbo].[audit_event_types] WHERE vcm_dvr_id NOT IN (SELECT idx FROM [dbo].[DVR])
	DELETE FROM [dbo].[audit_events] WHERE vcm_dvr_id NOT IN (SELECT idx FROM [dbo].[DVR])
	DELETE FROM [dbo].[audit_report_criteria_types] WHERE vcm_dvr_id NOT IN (SELECT idx FROM [dbo].[DVR])
	DELETE FROM [dbo].[audit_report_criterias] WHERE vcm_dvr_id NOT IN (SELECT idx FROM [dbo].[DVR])
	DELETE FROM [dbo].[audit_reports] WHERE vcm_dvr_id NOT IN (SELECT idx FROM [dbo].[DVR])
	DELETE FROM [dbo].[audit_users] WHERE vcm_dvr_id NOT IN (SELECT idx FROM [dbo].[DVR])
	DELETE FROM [dbo].[grouppermission] WHERE vcm_dvr_id NOT IN (SELECT idx FROM [dbo].[DVR])
	DELETE FROM [dbo].[groups] WHERE vcm_dvr_id NOT IN (SELECT idx FROM [dbo].[DVR])
	DELETE FROM [dbo].[perms] WHERE vcm_dvr_id NOT IN (SELECT idx FROM [dbo].[DVR])
	DELETE FROM [dbo].[pos_camera_mapping] WHERE vcm_dvr_id NOT IN (SELECT idx FROM [dbo].[DVR])
	DELETE FROM [dbo].[posdata] WHERE vcm_dvr_id NOT IN (SELECT idx FROM [dbo].[DVR])
	DELETE FROM [dbo].[usergroup] WHERE vcm_dvr_id NOT IN (SELECT idx FROM [dbo].[DVR])
	DELETE FROM [dbo].[users] WHERE vcm_dvr_id NOT IN (SELECT idx FROM [dbo].[DVR])
	DELETE FROM [dbo].[va_alarms] WHERE vcm_dvr_id NOT IN (SELECT idx FROM [dbo].[DVR])
	DELETE FROM [dbo].[va_faces] WHERE vcm_dvr_id NOT IN (SELECT idx FROM [dbo].[DVR])
	DELETE FROM [dbo].[va_pos] WHERE vcm_dvr_id NOT IN (SELECT idx FROM [dbo].[DVR])
	DELETE FROM [dbo].[vacounter] WHERE vcm_dvr_id NOT IN (SELECT idx FROM [dbo].[DVR])
	DELETE FROM [dbo].[vpos_alarm] WHERE vcm_dvr_id NOT IN (SELECT idx FROM [dbo].[DVR])
	DELETE FROM [dbo].[vpos_exception] WHERE vcm_dvr_id NOT IN (SELECT idx FROM [dbo].[DVR])
	DELETE FROM [dbo].[vpos_exceptionrecipient] WHERE vcm_dvr_id NOT IN (SELECT idx FROM [dbo].[DVR])
GO
