-- drop obsolete EEM stored procs/view/udfs

if exists (select * from sysobjects where name='spRPT_GetAllTimestampsForRange' and  xtype = 'P')
DROP PROCEDURE [dbo].[spRPT_GetAllTimestampsForRange]
GO

if exists (select * from sysobjects where name='spRPT_GetAllTimestamps' and  xtype = 'P')
DROP PROCEDURE [dbo].[spRPT_GetAllTimestamps]
GO

if exists (select * from sysobjects where name='spRA_RPT_DataLog_UTC' and  xtype = 'P')
DROP PROCEDURE [dbo].[spRA_RPT_DataLog_UTC]
GO

if exists (select * from sysobjects where name='spRA_RPT_CostData_UTC' and  xtype = 'P')
DROP PROCEDURE [dbo].[spRA_RPT_CostData_UTC]
GO

if exists (select * from sysobjects where name='spRA_RPT_CostData_Total_UTC' and  xtype = 'P')
DROP PROCEDURE [dbo].[spRA_RPT_CostData_Total_UTC]
GO

if exists (select * from sysobjects where name='sp_RA_General_GetSourceIDFromTreeNodeID' and  xtype = 'P')
DROP PROCEDURE [dbo].[sp_RA_General_GetSourceIDFromTreeNodeID]
GO

if exists (select * from sysobjects where name='spRA_CostData_Cost_UTC' and  xtype = 'P')
DROP PROCEDURE [dbo].[spRA_CostData_Cost_UTC]
GO

if exists (select * from sysobjects where name='spRA_CostData_BasedOnValues_UTC' and  xtype = 'P')
DROP PROCEDURE [dbo].[spRA_CostData_BasedOnValues_UTC]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udf_TimestampIDToDateTime]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[udf_TimestampIDToDateTime]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udf_DateTimeToTimestampID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[udf_DateTimeToTimestampID]
GO


if exists (select * from sysobjects where name='spTM_GetActiveTimeZones' and  xtype = 'P')
DROP PROCEDURE [dbo].[spTM_GetActiveTimeZones]
GO

if exists (select * from sysobjects where name='spRPT_GetAllTimezonesUsedBySources' and  xtype = 'P')
DROP PROCEDURE [dbo].[spRPT_GetAllTimezonesUsedBySources]
GO

if exists (select * from sysobjects where name='spAGG_VirtualSource_GetDependentsByName' and  xtype = 'P')
DROP PROCEDURE [dbo].[spAGG_VirtualSource_GetDependentsByName]
GO

if exists (select * from sysobjects where name='spAGG_VirtualSource_GetConstituents' and  xtype = 'P')
DROP PROCEDURE [dbo].[spAGG_VirtualSource_GetConstituents]
GO

if exists (select * from sysobjects where name='spAGG_VirtualSource_Add' and  xtype = 'P')
DROP PROCEDURE [dbo].[spAGG_VirtualSource_Add]
GO

if exists (select * from sysobjects where name='spAGG_VirtualSource_AddConstituent' and  xtype = 'P')
DROP PROCEDURE [dbo].[spAGG_VirtualSource_AddConstituent]
GO

if exists (select * from sysobjects where name='spAGG_VirtualSource_CheckClosure' and  xtype = 'P')
DROP PROCEDURE [dbo].[spAGG_VirtualSource_CheckClosure]
GO

if exists (select * from sysobjects where name='spAGG_AggregationByName' and  xtype = 'P')
DROP PROCEDURE [dbo].[spAGG_AggregationByName]
GO

if exists (select * from sysobjects where name='spAGG_Aggregation' and  xtype = 'P')
DROP PROCEDURE [dbo].[spAGG_Aggregation]
GO

if exists (select * from sysobjects where name='spRA_Retrieve_DimensionData_Filtered' and  xtype = 'P')
DROP PROCEDURE [dbo].[spRA_Retrieve_DimensionData_Filtered]
GO

if exists (select * from sysobjects where name='spRA_Retrieve_DimensionData' and  xtype = 'P')
DROP PROCEDURE [dbo].[spRA_Retrieve_DimensionData]
GO


if exists (select * from sysobjects where id=object_id('[dbo].[vTM_Dimension]') and OBJECTPROPERTY(id, 'IsView')=1)
DROP VIEW [dbo].[vTM_Dimension]
GO


if exists (select * from sysobjects where name='spRA_RPT_DataLog_SourceLocalTimezone' and  xtype = 'P')
DROP PROCEDURE [dbo].[spRA_RPT_DataLog_SourceLocalTimezone]
GO

if exists (select * from sysobjects where name='spRA_RPT_DataLog_SlowlyChangingValue_SourceLocal' and  xtype = 'P')
DROP PROCEDURE [dbo].[spRA_RPT_DataLog_SlowlyChangingValue_SourceLocal]
GO

if exists (select * from sysobjects where name='spRA_RPT_CostData_Total_SourceLocalTimezone' and  xtype = 'P')
DROP PROCEDURE [dbo].[spRA_RPT_CostData_Total_SourceLocalTimezone]
GO

if exists (select * from sysobjects where name='spRA_RPT_CostData_SourceLocalTimezone' and  xtype = 'P')
DROP PROCEDURE [dbo].[spRA_RPT_CostData_SourceLocalTimezone]
GO

if exists (select * from sysobjects where name='spRA_RPT_CostData_SlowlyChangingValue_SourceLocal' and  xtype = 'P')
DROP PROCEDURE [dbo].[spRA_RPT_CostData_SlowlyChangingValue_SourceLocal]
GO

if exists (select * from sysobjects where name='spTM_UpdateTimezone' and  xtype = 'P')
DROP PROCEDURE [dbo].[spTM_UpdateTimezone]
GO

if exists (select * from sysobjects where name='spTM_InsertUpdateDimensionValue' and  xtype = 'P')
DROP PROCEDURE [dbo].[spTM_InsertUpdateDimensionValue]
GO

if exists (select * from sysobjects where name='spTM_InsertUpdateDimensionType' and  xtype = 'P')
DROP PROCEDURE [dbo].[spTM_InsertUpdateDimensionType]
GO

if exists (select * from sysobjects where name='spTM_InsertUpdateDimensionInstances' and  xtype = 'P')
DROP PROCEDURE [dbo].[spTM_InsertUpdateDimensionInstances]
GO

if exists (select * from sysobjects where name='spRPT_GetTimeDimensionRollupTSFormat' and  xtype = 'P')
DROP PROCEDURE [dbo].[spRPT_GetTimeDimensionRollupTSFormat]
GO

if exists (select * from sysobjects where name='spAGG_VirtualSource_UpdateConstituent2' and  xtype = 'P')
DROP PROCEDURE [dbo].[spAGG_VirtualSource_UpdateConstituent2]
GO

if exists (select * from sysobjects where name='spAGG_VirtualSource_UpdateConstituent' and  xtype = 'P')
DROP PROCEDURE [dbo].[spAGG_VirtualSource_UpdateConstituent]
GO

if exists (select * from sysobjects where name='spAGG_VirtualSource_RetrieveAll' and  xtype = 'P')
DROP PROCEDURE [dbo].[spAGG_VirtualSource_RetrieveAll]
GO

if exists (select * from sysobjects where name='spAGG_VirtualSource_GetDependentsByDate' and  xtype = 'P')
DROP PROCEDURE [dbo].[spAGG_VirtualSource_GetDependentsByDate]
GO

if exists (select * from sysobjects where name='spAGG_VirtualSource_GetDependents' and  xtype = 'P')
DROP PROCEDURE [dbo].[spAGG_VirtualSource_GetDependents]
GO

if exists (select * from sysobjects where name='spAGG_VirtualSource_GetConstituentsByDate' and  xtype = 'P')
DROP PROCEDURE [dbo].[spAGG_VirtualSource_GetConstituentsByDate]
GO

if exists (select * from sysobjects where name='spAGG_VirtualSource_ExpireConstituent' and  xtype = 'P')
DROP PROCEDURE [dbo].[spAGG_VirtualSource_ExpireConstituent]
GO

if exists (select * from sysobjects where name='spAGG_VirtualSource_Expire' and  xtype = 'P')
DROP PROCEDURE [dbo].[spAGG_VirtualSource_Expire]
GO

if exists (select * from sysobjects where name='spAGG_VirtualSource_DeleteConstituent2' and  xtype = 'P')
DROP PROCEDURE [dbo].[spAGG_VirtualSource_DeleteConstituent2]
GO

if exists (select * from sysobjects where name='spAGG_VirtualSource_DeleteConstituent' and  xtype = 'P')
DROP PROCEDURE [dbo].[spAGG_VirtualSource_DeleteConstituent]
GO

if exists (select * from sysobjects where name='spAGG_VirtualSource_DeleteAllConstituents' and  xtype = 'P')
DROP PROCEDURE [dbo].[spAGG_VirtualSource_DeleteAllConstituents]
GO

if exists (select * from sysobjects where name='spAGG_VirtualSource_Delete' and  xtype = 'P')
DROP PROCEDURE [dbo].[spAGG_VirtualSource_Delete]
GO

if exists (select * from sysobjects where name='spAGG_Aggregation_Sum' and  xtype = 'P')
DROP PROCEDURE [dbo].[spAGG_Aggregation_Sum]
GO

if exists (select * from sysobjects where name='spAGG_Aggregation_Min' and  xtype = 'P')
DROP PROCEDURE [dbo].[spAGG_Aggregation_Min]
GO

if exists (select * from sysobjects where name='spAGG_Aggregation_Max' and  xtype = 'P')
DROP PROCEDURE [dbo].[spAGG_Aggregation_Max]
GO

if exists (select * from sysobjects where name='spAGG_Aggregation_Count' and  xtype = 'P')
DROP PROCEDURE [dbo].[spAGG_Aggregation_Count]
GO

if exists (select * from sysobjects where name='spAGG_Aggregation_Avg' and  xtype = 'P')
DROP PROCEDURE [dbo].[spAGG_Aggregation_Avg]
GO

if exists (select * from sysobjects where name='spAGG_VirtualSource_GetConstant' and  xtype = 'P')
DROP PROCEDURE [dbo].[spAGG_VirtualSource_GetConstant]
GO

if exists (select * from sysobjects where name='spRA_Retrieve_DimensionData_WithIds' and  xtype = 'P')
DROP PROCEDURE [dbo].[spRA_Retrieve_DimensionData_WithIds]
GO

if exists (select * from sysobjects where name='spRA_Retrieve_DimensionData_Filtered_WithIds' and  xtype = 'P')
DROP PROCEDURE [dbo].[spRA_Retrieve_DimensionData_Filtered_WithIds]
GO

if exists (select * from sysobjects where name='spRA_RPT_DataLog_SlowlyChangingValue' and  xtype = 'P')
DROP PROCEDURE [dbo].[spRA_RPT_DataLog_SlowlyChangingValue]
GO

if exists (select * from sysobjects where name='spRA_RPT_DataLog_OtherTimezone' and  xtype = 'P')
DROP PROCEDURE [dbo].[spRA_RPT_DataLog_OtherTimezone]
GO

if exists (select * from sysobjects where name='spRA_RPT_CostData_Total_OtherTimezone' and  xtype = 'P')
DROP PROCEDURE [dbo].[spRA_RPT_CostData_Total_OtherTimezone]
GO

if exists (select * from sysobjects where name='spRA_RPT_CostData_SlowlyChangingValue' and  xtype = 'P')
DROP PROCEDURE [dbo].[spRA_RPT_CostData_SlowlyChangingValue]
GO

if exists (select * from sysobjects where name='spRA_RPT_CostData_OtherTimezone' and  xtype = 'P')
DROP PROCEDURE [dbo].[spRA_RPT_CostData_OtherTimezone]
GO

if exists (select * from sysobjects where name='spTM_InsertUpdateTimezoneValue' and  xtype = 'P')
DROP PROCEDURE [dbo].[spTM_InsertUpdateTimezoneValue]
GO

-- drop obsolete tables

--Dropping foreign keys from [dbo].[AGG_VirtualSource]'

if exists (select * from sysobjects where name='FK_AGG_VirtualSource_SRC_Source_SourceID' and  xtype = 'F')
ALTER TABLE [dbo].[AGG_VirtualSource] DROP
CONSTRAINT [FK_AGG_VirtualSource_SRC_Source_SourceID]
GO

if exists (select * from sysobjects where name='FK_AGG_VirtualSource_SRC_Source_ConstituentSourceID' and  xtype = 'F')
ALTER TABLE [dbo].[AGG_VirtualSource] DROP
CONSTRAINT [FK_AGG_VirtualSource_SRC_Source_ConstituentSourceID]
GO

--Dropping foreign keys from [dbo].[TM_DimensionValues]'
if exists (select * from sysobjects where name='FK_TM_DimensionValues_TM_DimensionType' and  xtype = 'F')
ALTER TABLE [dbo].[TM_DimensionValues] DROP
CONSTRAINT [FK_TM_DimensionValues_TM_DimensionType]
GO

-- Dropping foreign keys from [dbo].[TM_TimestampDefinition]'
if exists (select * from sysobjects where name='FK_TM_TimestampDefinition_SRC_TimeZone' and  xtype = 'F')
ALTER TABLE [dbo].[TM_TimestampDefinition] DROP
CONSTRAINT [FK_TM_TimestampDefinition_SRC_TimeZone]
GO

--Dropping constraints from [dbo].[AGG_AggregationType]'
if exists (select * from sysobjects where name='PK_AGG_Aggregation_Type' and  xtype = 'PK')
ALTER TABLE [dbo].[AGG_AggregationType] DROP CONSTRAINT [PK_AGG_Aggregation_Type]

--Dropping constraints from [dbo].[TM_Dimensions]'

if exists (select * from sysobjects where name='PK_TM_Dimensions' and  xtype = 'PK')
ALTER TABLE [dbo].[TM_Dimensions] DROP CONSTRAINT [PK_TM_Dimensions]
GO

-- Dropping constraints from [dbo].[TM_DimensionType]'
if exists (select * from sysobjects where name='PK_TM_DimensionType' and  xtype = 'PK')
ALTER TABLE [dbo].[TM_DimensionType] DROP CONSTRAINT [PK_TM_DimensionType]
GO

--Dropping constraints from [dbo].[TM_DimensionValues]'
if exists (select * from sysobjects where name='PK_TM_DimensionValues' and  xtype = 'PK')
ALTER TABLE [dbo].[TM_DimensionValues] DROP CONSTRAINT [PK_TM_DimensionValues]
GO

-- Dropping constraints from [dbo].[TM_TimestampDefinition]'
if exists (select * from sysobjects where name='TM_TimestampDefinition' and  xtype = 'PK')
ALTER TABLE [dbo].[TM_TimestampDefinition] DROP CONSTRAINT [PK_TM_TimestampDefinition]
GO

-- Dropping index [IX_AGG_AggregationType_AggregationTypeID] from [dbo].[AGG_AggregationType]'
if exists (select * from sysindexes where name='IX_AGG_AggregationType_AggregationTypeID')
DROP INDEX [IX_AGG_AggregationType_AggregationTypeID] ON [dbo].[AGG_AggregationType]
GO

--Dropping index [IX_AGG_VirtualSource_SourceID] from [dbo].[AGG_VirtualSource]'
if exists (select * from sysindexes where name='IX_AGG_VirtualSource_SourceID')
DROP INDEX [IX_AGG_VirtualSource_SourceID] ON [dbo].[AGG_VirtualSource]
GO

-- Dropping index [IX_AGG_VirtualSource_ConstituentSourceID] from [dbo].[AGG_VirtualSource]'
if exists (select * from sysindexes where name='IX_AGG_VirtualSource_ConstituentSourceID')
DROP INDEX [IX_AGG_VirtualSource_ConstituentSourceID] ON [dbo].[AGG_VirtualSource]
GO

--Dropping index [IX_TM_Dimensions_2] from [dbo].[TM_Dimensions]'
if exists (select * from sysindexes where name='IX_TM_Dimensions_2')
DROP INDEX [IX_TM_Dimensions_2] ON [dbo].[TM_Dimensions]
GO

--Dropping index [IX_TM_Dimensions_1] from [dbo].[TM_Dimensions]'
if exists (select * from sysindexes where name='IX_TM_Dimensions_1')
DROP INDEX [IX_TM_Dimensions_1] ON [dbo].[TM_Dimensions]
GO

--Dropping index [IX_TM_Dimensions_TimestampUTC] from [dbo].[TM_Dimensions]'
if exists (select * from sysindexes where name='IX_TM_Dimensions_TimestampUTC]')
DROP INDEX [IX_TM_Dimensions_TimestampUTC] ON [dbo].[TM_Dimensions]
GO

--Dropping index [IX_TimestampDefinition_TimestampUTC] from [dbo].[TM_TimestampDefinition]'
if exists (select * from sysindexes where name='IX_TimestampDefinition_TimestampUTC]')
DROP INDEX [IX_TimestampDefinition_TimestampUTC] ON [dbo].[TM_TimestampDefinition]
GO

if exists (select * from sysobjects where name='TM_Dimensions' and  xtype = 'U')
DROP TABLE [dbo].[TM_Dimensions]
GO

if exists (select * from sysobjects where name='TM_DimensionType' and  xtype = 'U')
DROP TABLE [dbo].[TM_DimensionType]
GO

if exists (select * from sysobjects where name='TM_DimensionValues' and  xtype = 'U')
DROP TABLE [dbo].[TM_DimensionValues]
GO

if exists (select * from sysobjects where name='TM_TimestampDefinition' and  xtype = 'U')
DROP TABLE [dbo].[TM_TimestampDefinition]

if exists (select * from sysobjects where name='AGG_AggregationType' and  xtype = 'U')
DROP TABLE [dbo].[AGG_AggregationType]
GO

if exists (select * from sysobjects where name='AGG_VirtualSource' and  xtype = 'U')
DROP TABLE [dbo].[AGG_VirtualSource]
GO



