Wswsqlclean - reorganising and compressing some parts of the database
Created: Roy Wood Date: 18 FEB 2002
Updated: Roy Wood Date: 19 FEB 2002 Reason: Single Script
COMPATIBLE: SQL 6.5 / 7.0 / 2000
Task: Removal of records from MIMESWEEPER WSW
USAGE: Run .SQL Script from SQL Query Analyzer
Execute:
USE DB_NAME
EXEC sp_MSW_goaRemovedID_ClearDown 4,'MONTH'
Above script will delete all records Older than 4 Months
Stored procedure accepts 2 parameters (UNITS, DAY'/'WEEK'/'MONTH'/'YEAR')
**PLEASE ENSURE DATABASE IS FULLY BACKED UP PRIOR TO RUNNING THIS SCRIPT. THIS SCRIPT IS NOT SUPPORTED AND IS USED AT YOUR OWN RISK**
/****** Object: Table [dbo].[goaRemovedID] Script Date: 19/02/2002 10:41:42 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[goaRemovedID]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[goaRemovedID]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/****** Object: Table [dbo].[goaRemovedID] Script Date: 19/02/2002 10:41:47 ******/
CREATE TABLE [dbo].[goaRemovedID] (
[ID] [int] NOT NULL ,
[Status] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[goaRemovedID] WITH NOCHECK ADD
CONSTRAINT [DF_goaRemovedID_Status] DEFAULT (0) FOR [Status]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/* WOA CREATION */
/****** Object: Stored Procedure dbo.sp_MSW_woaAuthenticationFailures_Clean Script Date: 19/02/2002 10:17:17 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_MSW_woaAuthenticationFailures_Clean]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_MSW_woaAuthenticationFailures_Clean]
GO
/****** Object: Stored Procedure dbo.sp_MSW_woaMsgIdMap_Clean Script Date: 19/02/2002 10:17:17 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_MSW_woaMsgIdMap_Clean]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_MSW_woaMsgIdMap_Clean]
GO
/****** Object: Stored Procedure dbo.sp_MSW_woaStringCacheMap_Clean Script Date: 19/02/2002 10:17:17 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_MSW_woaStringCacheMap_Clean]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_MSW_woaStringCacheMap_Clean]
GO
/****** Object: Stored Procedure dbo.sp_MSW_woaUserMap_Clean Script Date: 19/02/2002 10:17:17 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_MSW_woaUserMap_Clean]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_MSW_woaUserMap_Clean]
GO
/****** Object: Stored Procedure dbo.sp_MSW_woa_CleanAll Script Date: 19/02/2002 10:17:17 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_MSW_woa_CleanAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_MSW_woa_CleanAll]
GO
/****** Object: Stored Procedure dbo.sp_MSW_goaRemoveID_DEL_woa_ALL Script Date: 19/02/2002 10:35:43 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_MSW_goaRemoveID_DEL_woa_ALL]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_MSW_goaRemoveID_DEL_woa_ALL]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/****** Object: Stored Procedure dbo.sp_MSW_woaAuthenticationFailures_Clean Script Date: 19/02/2002 10:17:17 ******/
-- Clean woaAuthenticationFailures Following deletion
-- Roy Wood
-- 13-FEB-2002
CREATE PROCEDURE sp_MSW_woaAuthenticationFailures_Clean AS
BEGIN
/* SELECT MapID FROM woaAuthenticationFailures */
DELETE FROM woaAuthenticationFailures
WHERE IPAddress NOT IN
(
SELECT DISTINCT MapID FROM goaMachineIDMap
)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/****** Object: Stored Procedure dbo.sp_MSW_woaMsgIdMap_Clean Script Date: 19/02/2002 10:17:17 ******/
-- Clean woaStringCacheMap Following deletion
-- Roy Wood
-- 13-FEB-2002
CREATE PROCEDURE sp_MSW_woaMsgIdMap_Clean AS
BEGIN
/* SELECT MapID FROM woaStringCacheMap */
DELETE FROM woaMsgIdMap
WHERE MapID NOT IN
(
SELECT DISTINCT ID FROM woaTransactionDetail
)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/****** Object: Stored Procedure dbo.sp_MSW_woaStringCacheMap_Clean Script Date: 19/02/2002 10:17:17 ******/
-- Clean woaStringCacheMap Following deletion
-- Roy Wood
-- 13-FEB-2002
CREATE PROCEDURE sp_MSW_woaStringCacheMap_Clean AS
BEGIN
/* SELECT MapID FROM woaStringCacheMap */
DELETE FROM woaStringCacheMap
WHERE MapID NOT IN
(
SELECT DISTINCT RequestType FROM woaTransactionDetail
UNION
SELECT DISTINCT ResponseSourceID FROM woaTransactionDetail
)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/****** Object: Stored Procedure dbo.sp_MSW_woaUserMap_Clean Script Date: 19/02/2002 10:17:17 ******/
-- Clean woaUserMap Following deletion
-- Roy Wood
-- 13-FEB-2002
CREATE PROCEDURE sp_MSW_woaUserMap_Clean AS
BEGIN
/* SELECT MapID FROM woaUserMap */
DELETE FROM woaUserMap
WHERE MapID NOT IN
(
SELECT DISTINCT UserID FROM woaAuthenticationFailures
UNION
SELECT DISTINCT UserID FROM woaTransactionDetail
)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/****** Object: Stored Procedure dbo.sp_MSW_woa_CleanAll Script Date: 19/02/2002 10:17:17 ******/
-- Clean All Unlinked Info Following deletion
-- Roy Wood
--13-FEB-2002
CREATE PROCEDURE sp_MSW_woa_CleanAll AS
/* woa List */
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[woaStringCacheMap]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
EXEC sp_MSW_woaStringCacheMap_Clean
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[woaAuthenticationFailures]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
EXEC sp_MSW_woaAuthenticationFailures_Clean
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[woaUserMap]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
EXEC sp_MSW_woaUserMap_Clean
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/****** Object: Stored Procedure dbo.sp_MSW_goaRemoveID_DEL_woa_ALL Script Date: 19/02/2002 10:35:43 ******/
-- Deletion Of Records By ID One Table Complete
-- Update Status 1=Deleted
-- Roy Wood
-- 15-FEB-2002
CREATE PROCEDURE sp_MSW_goaRemoveID_DEL_woa_ALL AS
DELETE woaTransactionDetail
FROM woaTransactionDetail
INNER JOIN goaRemovedID
ON woaTransactionDetail.ResponseEngineID=goaRemovedID.ID
WHERE Status=0
DELETE woaTransactionDetail
FROM woaTransactionDetail
INNER JOIN goaRemovedID
ON woaTransactionDetail.RequestEngineID=goaRemovedID.ID
WHERE Status=0
DELETE woaTransactionIdMap
FROM woaTransactionIdMap
INNER JOIN goaRemovedID
ON woaTransactionIdMap.MapID=goaRemovedID.ID
WHERE Status=0
DELETE woaMsgIdMap
FROM woaMsgIdMap
INNER JOIN goaRemovedID
ON woaMsgIdMap.MapID=goaRemovedID.ID
WHERE Status=0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/* GOA CREATION*/
/****** Object: Stored Procedure dbo.sp_MSW_goaClassificationMap_Clean Script Date: 19/02/2002 10:15:35 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_MSW_goaClassificationMap_Clean]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_MSW_goaClassificationMap_Clean]
GO
/****** Object: Stored Procedure dbo.sp_MSW_goaClassifications_Clean Script Date: 19/02/2002 10:15:35 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_MSW_goaClassifications_Clean]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_MSW_goaClassifications_Clean]
GO
/****** Object: Stored Procedure dbo.sp_MSW_goaCustomEvents_Clean Script Date: 19/02/2002 10:15:35 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_MSW_goaCustomEvents_Clean]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_MSW_goaCustomEvents_Clean]
GO
/****** Object: Stored Procedure dbo.sp_MSW_goaEventDetail_Clean Script Date: 19/02/2002 10:15:35 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_MSW_goaEventDetail_Clean]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_MSW_goaEventDetail_Clean]
GO
/****** Object: Stored Procedure dbo.sp_MSW_goaEventMap_Clean Script Date: 19/02/2002 10:15:35 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_MSW_goaEventMap_Clean]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_MSW_goaEventMap_Clean]
GO
/****** Object: Stored Procedure dbo.sp_MSW_goaFormatDetail_Clean Script Date: 19/02/2002 10:15:35 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_MSW_goaFormatDetail_Clean]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_MSW_goaFormatDetail_Clean]
GO
/****** Object: Stored Procedure dbo.sp_MSW_goaFormatTypeMap_Clean Script Date: 19/02/2002 10:15:35 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_MSW_goaFormatTypeMap_Clean]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_MSW_goaFormatTypeMap_Clean]
GO
/****** Object: Stored Procedure dbo.sp_MSW_goaMachineIdMap_Clean Script Date: 19/02/2002 10:15:35 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_MSW_goaMachineIdMap_Clean]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_MSW_goaMachineIdMap_Clean]
GO
/****** Object: Stored Procedure dbo.sp_MSW_goaRemoveID_DEL_goa_ALL Script Date: 19/02/2002 10:15:35 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_MSW_goaRemoveID_DEL_goa_ALL]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_MSW_goaRemoveID_DEL_goa_ALL]
GO
/****** Object: Stored Procedure dbo.sp_MSW_goaRemovedID_ClearDown Script Date: 19/02/2002 10:15:35 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_MSW_goaRemovedID_ClearDown]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_MSW_goaRemovedID_ClearDown]
GO
/****** Object: Stored Procedure dbo.sp_MSW_goaRemovedID_List Script Date: 19/02/2002 10:15:35 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_MSW_goaRemovedID_List]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_MSW_goaRemovedID_List]
GO
/****** Object: Stored Procedure dbo.sp_MSW_goaRemovedID_List_Empty Script Date: 19/02/2002 10:15:35 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_MSW_goaRemovedID_List_Empty]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_MSW_goaRemovedID_List_Empty]
GO
/****** Object: Stored Procedure dbo.sp_MSW_goaUnhandledEvents_Clean Script Date: 19/02/2002 10:15:35 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_MSW_goaUnhandledEvents_Clean]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_MSW_goaUnhandledEvents_Clean]
GO
/****** Object: Stored Procedure dbo.sp_MSW_goa_CleanAll Script Date: 19/02/2002 10:15:35 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_MSW_goa_CleanAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_MSW_goa_CleanAll]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/****** Object: Stored Procedure dbo.sp_MSW_goaClassificationMap_Clean Script Date: 19/02/2002 10:15:35 ******/
-- Clean goaClassificationMap Following deletion
-- Roy Wood
-- 12-FEB-2002
CREATE PROCEDURE sp_MSW_goaClassificationMap_Clean AS
BEGIN
/* SELECT MapID FROM goaClassificationMap */
DELETE FROM goaClassificationMap
WHERE MapID NOT IN
(
SELECT DISTINCT MappedValue FROM goaClassifications
)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/****** Object: Stored Procedure dbo.sp_MSW_goaClassifications_Clean Script Date: 19/02/2002 10:15:35 ******/
-- Clean goaClassifications Following deletion
-- Roy Wood
-- 13-FEB-2002
CREATE PROCEDURE sp_MSW_goaClassifications_Clean AS
BEGIN
/* SELECT MapID FROM goaClassifications */
DELETE FROM goaClassifications
WHERE MapID NOT IN
(
SELECT DISTINCT ID FROM goaEngineDetail
)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/****** Object: Stored Procedure dbo.sp_MSW_goaCustomEvents_Clean Script Date: 19/02/2002 10:15:35 ******/
-- Clean goaCustomEvents Following deletion
-- Roy Wood
-- 13-FEB-2002
CREATE PROCEDURE sp_MSW_goaCustomEvents_Clean AS
BEGIN
/* SELECT MapID FROM goaCustomEvents */
DELETE FROM goaCustomEvents
WHERE ID NOT IN
(
SELECT DISTINCT ID FROM goaEngineDetail
)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/****** Object: Stored Procedure dbo.sp_MSW_goaEventDetail_Clean Script Date: 19/02/2002 10:15:35 ******/
-- Clean goaEventDetail Following deletion
-- Roy Wood
-- 13-FEB-2002
CREATE PROCEDURE sp_MSW_goaEventDetail_Clean AS
BEGIN
/* SELECT MapID FROM goaEventDetail */
DELETE FROM goaEventDetail
WHERE ID NOT IN
(
SELECT DISTINCT ID FROM goaEngineDetail
)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/****** Object: Stored Procedure dbo.sp_MSW_goaEventMap_Clean Script Date: 19/02/2002 10:15:35 ******/
-- Clean goaEventMap Following deletion
-- Roy Wood
-- 12-FEB-2002
CREATE PROCEDURE sp_MSW_goaEventMap_Clean AS
BEGIN
/* SELECT MapID FROM goaEventMap */
DELETE FROM goaEventMap
WHERE MapID NOT IN
(
SELECT DISTINCT ScenarioTrigger FROM goaEventDetail
UNION
SELECT DISTINCT Action FROM goaEventDetail
UNION
SELECT DISTINCT Description FROM goaEventDetail
)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/****** Object: Stored Procedure dbo.sp_MSW_goaFormatDetail_Clean Script Date: 19/02/2002 10:15:35 ******/
-- Clean goaFormatDetail Following deletion
-- Roy Wood
-- 13-FEB-2002
CREATE PROCEDURE sp_MSW_goaFormatDetail_Clean AS
BEGIN
/* SELECT MapID FROM goaFormatDetail */
DELETE FROM goaFormatDetail
WHERE ID NOT IN
(
SELECT DISTINCT ID FROM goaEngineDetail
)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/****** Object: Stored Procedure dbo.sp_MSW_goaFormatTypeMap_Clean Script Date: 19/02/2002 10:15:35 ******/
-- Clean goaFormatTypeMap Following deletion
-- Roy Wood
-- 12-FEB-2002
CREATE PROCEDURE sp_MSW_goaFormatTypeMap_Clean AS
BEGIN
/* SELECT MapID FROM goaFormatTypeMap */
DELETE FROM goaFormatTypeMap
WHERE MapID NOT IN
(
SELECT DISTINCT FormatManager FROM goaFormatDetail
UNION
SELECT DISTINCT FormatType FROM goaFormatDetail
UNION
SELECT DISTINCT FormatSubType FROM goaFormatDetail
)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/****** Object: Stored Procedure dbo.sp_MSW_goaUnhandledEvents_Clean Script Date: 19/02/2002 10:15:36 ******/
-- Clean goaUnhandledEvents Following deletion
-- Roy Wood
-- 13-FEB-2002
CREATE PROCEDURE sp_MSW_goaUnhandledEvents_Clean AS
BEGIN
/* SELECT MapID FROM goaUnhandledEvents */
DELETE FROM goaUnhandledEvents
WHERE ID NOT IN
(
SELECT DISTINCT ID FROM goaEngineDetail
)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/****** Object: Stored Procedure dbo.sp_MSW_goaMachineIdMap_Clean Script Date: 19/02/2002 10:15:35 ******/
-- Clean goaMachineIdMap Following deletion
-- Roy Wood
-- 12-FEB-2002
CREATE PROCEDURE sp_MSW_goaMachineIdMap_Clean AS
BEGIN
/* SELECT MapID FROM goaMachineIdMap */
DELETE FROM goaMachineIdMap
WHERE MapID NOT IN
(
SELECT DISTINCT MachineID FROM goaEngineDetail
UNION
SELECT DISTINCT MachineID FROM goaAuditHistory
/* UNION
SELECT DISTINCT MachineID FROM goaAuditHistory */
)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/****** Object: Stored Procedure dbo.sp_MSW_goaRemoveID_DEL_goa_ALL Script Date: 19/02/2002 10:15:35 ******/
-- Deletion Of Records By ID One Table Complete
-- Update Status 1=Deleted
-- Roy Wood
-- 07-FEB-2002
CREATE PROCEDURE sp_MSW_goaRemoveID_DEL_goa_ALL AS
DELETE goaEngineDetail
FROM goaEngineDetail INNER JOIN goaRemovedID
ON goaEngineDetail.ID=goaRemovedID.ID
WHERE Status=0
DELETE goaFormatDetail
FROM goaFormatDetail INNER JOIN goaRemovedID
ON goaFormatDetail.ID=goaRemovedID.ID
WHERE Status=0
DELETE goaClassifications
FROM goaClassifications INNER JOIN goaRemovedID
ON goaClassifications.MapID=goaRemovedID.ID
WHERE Status=0
DELETE goaEventDetail
FROM goaEventDetail INNER JOIN goaRemovedID
ON goaEventDetail.ID=goaRemovedID.ID
WHERE Status=0
DELETE goaUnhandledEvents
FROM goaUnhandledEvents INNER JOIN goaRemovedID
ON goaUnhandledEvents.ID=goaRemovedID.ID
WHERE Status=0
DELETE goaCustomEvents
FROM goaCustomEvents INNER JOIN goaRemovedID
ON goaCustomEvents.ID=goaRemovedID.ID
WHERE Status=0
/*
EXEC sp_MSW_goaClassificationMap_Clean
EXEC sp_MSW_goaFormatTypeMap_Clean
EXEC sp_MSW_goaEventMap_Clean
*/
EXEC sp_MSW_goaMachineIdMap_Clean
/*
EXEC sp_MSW_goaMachineIdMap_Clean
EXEC sp_MSW_goaMachineIdMap_Clean
*/
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/****** Object: Stored Procedure dbo.sp_MSW_goaRemovedID_List Script Date: 19/02/2002 10:15:35 ******/
-- Store List Of Values Prior to deletion
-- Status 0=Pending 1=Deleted 2=Archived
-- Roy Wood
-- 07-FEB-2002
CREATE PROCEDURE sp_MSW_goaRemovedID_List
@Duration INT,
@Units VARCHAR(5)
AS
SELECT @Duration = 0 - @Duration
IF UPPER(@Units) = 'DAY'
BEGIN
INSERT INTO goaRemovedID (ID)
SELECT ID
FROM goaEngineDetail
WHERE StartTime <= DATEADD(Day, @Duration, GETDATE())
END
IF UPPER(@Units) = 'WEEK'
BEGIN
INSERT INTO goaRemovedID (ID)
SELECT ID
FROM goaEngineDetail
WHERE StartTime <= DATEADD(Week, @Duration, GETDATE())
END
IF UPPER(@Units) = 'MONTH'
BEGIN
INSERT INTO goaRemovedID (ID)
SELECT ID
FROM goaEngineDetail
WHERE StartTime <= DATEADD(Month, @Duration, GETDATE())
END
IF UPPER(@Units) = 'YEAR'
BEGIN
INSERT INTO goaRemovedID (ID)
SELECT ID
FROM goaEngineDetail
WHERE StartTime <= DATEADD(Year, @Duration, GETDATE())
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/****** Object: Stored Procedure dbo.sp_MSW_goaRemovedID_List_Empty Script Date: 19/02/2002 10:15:36 ******/
-- Clean goaRemovedID Remove all deletion information
-- Roy Wood
-- 13-FEB-2002
CREATE PROCEDURE sp_MSW_goaRemovedID_List_Empty AS
DELETE FROM goaRemovedID WHERE Status=0
DELETE FROM goaRemovedID WHERE Status=1
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/****** Object: Stored Procedure dbo.sp_MSW_goa_CleanAll Script Date: 19/02/2002 10:15:36 ******/
-- Clean All Unlinked Info Following deletion
-- Roy Wood
--13-FEB-2002
CREATE PROCEDURE sp_MSW_goa_CleanAll AS
/* goa List */
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[goaMachineIdMap]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
EXEC sp_MSW_goaMachineIdMap_Clean
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[goaCustomEvents]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
EXEC sp_MSW_goaCustomEvents_Clean
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[goaUnhandledEvents]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
EXEC sp_MSW_goaUnhandledEvents_Clean
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[goaEventDetail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
EXEC sp_MSW_goaEventDetail_Clean
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[goaEventMap]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
EXEC sp_MSW_goaEventMap_Clean
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[goaFormatDetail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
EXEC sp_MSW_goaFormatDetail_Clean
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[goaFormatTypeMap]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
EXEC sp_MSW_goaFormatTypeMap_Clean
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[goaClassifications]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
EXEC sp_MSW_goaClassifications_Clean
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[goaClassificationMap]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
EXEC sp_MSW_goaClassificationMap_Clean
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/****** Object: Stored Procedure dbo.sp_MSW_goaRemovedID_ClearDown Script Date: 19/02/2002 10:15:35 ******/
-- Deletion Of Records Script for duration
-- Update Status 1=Deleted
-- Roy Wood
-- 14-FEB-2002
CREATE PROCEDURE sp_MSW_goaRemovedID_ClearDown
@Duration INT,
@Units VARCHAR(5)
AS
/* Need to clear down old records and check non-pending */
/* Delete excess records from built list first*/
SET NOCOUNT ON
DECLARE @INT INT
SELECT @INT=COUNT(*) FROM goaRemovedID WHERE Status = 0
/* IF Few outstanding records Build List Items to delete Else skip*/
IF @INT < 2
EXEC sp_MSW_goaRemovedID_List @Duration, @Units
/* Delete records from built list*/
EXEC sp_MSW_goaRemoveID_DEL_goa_ALL
/*
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[moaMessage]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
EXEC sp_MSW_goaRemoveID_DEL_moa_ALL
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[woaTransactionDetail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
EXEC sp_MSW_goaRemoveID_DEL_woa_ALL
SET NOCOUNT OFF
UPDATE goaRemovedID
SET Status=1
WHERE Status =0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Back