/****** Script for cleaning up obsolete request data  ******/
USE []--{DATABASE NAME}
GO

declare @totalRows int, @rowsToKeep int

set @totalRows = (SELECT rows FROM sysindexes WHERE id = OBJECT_ID('REQUEST_PARAM') AND indid < 2)

set @rowsToKeep = (select COUNT(*) from REQUEST_PARAM where REQUEST_ID in(select ID from REQUEST_STATUS))
	
	SET ANSI_NULLS ON
	SET QUOTED_IDENTIFIER ON
	SET ANSI_PADDING ON


	CREATE TABLE REQUEST_PARAM_temp(
		[ID] [bigint] IDENTITY(1,1) NOT NULL,
		[REQUEST_ID] [bigint] NOT NULL,
		[SERVER] [smallint] NOT NULL,
		[PARAM_KEY] [varchar](255) NOT NULL,
		[PARAM_VALUE] [varchar](512) NOT NULL,
	PRIMARY KEY CLUSTERED 
	(
		[ID] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]


	CREATE NONCLUSTERED INDEX [IDX_REQUEST_PARAM] ON REQUEST_PARAM_temp 
	(
		[REQUEST_ID] ASC,
		[PARAM_KEY] ASC,
		[PARAM_VALUE] ASC
	)WITH (PAD_INDEX  = ON, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]


	CREATE NONCLUSTERED INDEX [IDX_REQUEST_PARAM_2] ON REQUEST_PARAM_temp 
	(
		[SERVER] ASC
	)WITH (PAD_INDEX  = ON, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]




	SET IDENTITY_INSERT REQUEST_PARAM_temp on
	insert into REQUEST_PARAM_temp (ID,REQUEST_ID,SERVER,PARAM_KEY,PARAM_VALUE) select ID,REQUEST_ID,SERVER,PARAM_KEY,PARAM_VALUE from REQUEST_PARAM where REQUEST_ID in(select ID from REQUEST_STATUS)
	TRUNCATE TABLE REQUEST_PARAM
	drop table REQUEST_PARAM
	SET IDENTITY_INSERT REQUEST_PARAM_temp OFF

	exec sp_rename 'REQUEST_PARAM_temp','REQUEST_PARAM';


set @totalRows = (SELECT rows FROM sysindexes WHERE id = OBJECT_ID('REQUEST_AMF') AND indid < 2)

set @rowsToKeep = (select COUNT(*) from REQUEST_AMF where REQUEST_ID in(select ID from REQUEST_STATUS))

	--Delete from AMF table
	CREATE TABLE REQUEST_AMF_temp(
		[ID] [bigint] IDENTITY(1,1) NOT NULL,
		[REQUEST_ID] [bigint] NOT NULL,
		[SERVER] [smallint] NOT NULL,
		[FUNCTION_INDEX] [smallint] NOT NULL,
		[DESTINATION] [varchar](50) NOT NULL,
		[SERVICE_NAME] [varchar](100) NOT NULL,
		[SERVICE_METHOD] [varchar](50) NOT NULL,
		[TARGET] [varchar](50) NOT NULL,
	PRIMARY KEY CLUSTERED 
	(
		[ID] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]

	CREATE NONCLUSTERED INDEX [IDX_REQUEST_AMF_1] ON REQUEST_AMF_temp 
	(
		[REQUEST_ID] ASC
	)WITH (PAD_INDEX  = ON, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]


	SET IDENTITY_INSERT REQUEST_AMF_temp on
	insert into REQUEST_AMF_temp ([ID],[REQUEST_ID],[SERVER],[FUNCTION_INDEX],[DESTINATION],[SERVICE_NAME],[SERVICE_METHOD],[TARGET]) 
	select [ID],[REQUEST_ID],[SERVER],[FUNCTION_INDEX],[DESTINATION],[SERVICE_NAME],[SERVICE_METHOD],[TARGET] from REQUEST_AMF where REQUEST_ID in(select ID from REQUEST_STATUS)
	TRUNCATE TABLE REQUEST_AMF
	drop table REQUEST_AMF
	SET IDENTITY_INSERT REQUEST_AMF_temp OFF

	exec sp_rename 'REQUEST_AMF_temp','REQUEST_AMF'

	SET ANSI_PADDING OFF;
