USE [REPLACE Cloud Assert Billing DB Name Here!!!]
GO

DROP TABLE [Csp].[CustomerSubscriptions]
DELETE [dbo].[__MigrationHistory]
WHERE (([MigrationId] = N'201708180724378_AddCSPCustomerSubscriptionsTable') AND ([ContextKey] = N'CloudAssert.WAP.Billing.Persistence.Sql.Migrations.Configuration'))
IF object_id(N'[dbo].[FK_dbo.ExclusiveSubscriptionCredits_dbo.Subscriptions_SubscriptionId]', N'F') IS NOT NULL
    ALTER TABLE [dbo].[ExclusiveSubscriptionCredits] DROP CONSTRAINT [FK_dbo.ExclusiveSubscriptionCredits_dbo.Subscriptions_SubscriptionId]
IF object_id(N'[dbo].[FK_dbo.ExclusiveSubscriptionCredits_dbo.SubscriptionPromotions_SubscriptionPromotion_Id]', N'F') IS NOT NULL
    ALTER TABLE [dbo].[ExclusiveSubscriptionCredits] DROP CONSTRAINT [FK_dbo.ExclusiveSubscriptionCredits_dbo.SubscriptionPromotions_SubscriptionPromotion_Id]
IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_SubscriptionId' AND object_id = object_id(N'[dbo].[ExclusiveSubscriptionCredits]', N'U'))
    DROP INDEX [IX_SubscriptionId] ON [dbo].[ExclusiveSubscriptionCredits]
IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_SubscriptionPromotion_Id' AND object_id = object_id(N'[dbo].[ExclusiveSubscriptionCredits]', N'U'))
    DROP INDEX [IX_SubscriptionPromotion_Id] ON [dbo].[ExclusiveSubscriptionCredits]
DROP TABLE [dbo].[ExclusiveSubscriptionCredits]
DELETE [dbo].[__MigrationHistory]
WHERE (([MigrationId] = N'201708111032010_AddExclusiveSubscriptionCreditsTable') AND ([ContextKey] = N'CloudAssert.WAP.Billing.Persistence.Sql.Migrations.Configuration'))
DECLARE @var0 nvarchar(128)
SELECT @var0 = name
FROM sys.default_constraints
WHERE parent_object_id = object_id(N'dbo.Subscriptions')
AND col_name(parent_object_id, parent_column_id) = 'OneTimeFeeExpiryDate';
IF @var0 IS NOT NULL
    EXECUTE('ALTER TABLE [dbo].[Subscriptions] DROP CONSTRAINT [' + @var0 + ']')
ALTER TABLE [dbo].[Subscriptions] DROP COLUMN [OneTimeFeeExpiryDate]
DECLARE @var1 nvarchar(128)
SELECT @var1 = name
FROM sys.default_constraints
WHERE parent_object_id = object_id(N'dbo.Subscriptions')
AND col_name(parent_object_id, parent_column_id) = 'ShouldApplyOneTimeFee';
IF @var1 IS NOT NULL
    EXECUTE('ALTER TABLE [dbo].[Subscriptions] DROP CONSTRAINT [' + @var1 + ']')
ALTER TABLE [dbo].[Subscriptions] DROP COLUMN [ShouldApplyOneTimeFee]
DELETE [dbo].[__MigrationHistory]
WHERE (([MigrationId] = N'201708070612457_AddShouldApplyOneTimeFeeColumnInSubscriptionsTable') AND ([ContextKey] = N'CloudAssert.WAP.Billing.Persistence.Sql.Migrations.Configuration'))
IF object_id(N'[Csp].[FK_Csp.BaseOfferCategory_Csp.BaseOffer_OfferId]', N'F') IS NOT NULL
    ALTER TABLE [Csp].[BaseOfferCategory] DROP CONSTRAINT [FK_Csp.BaseOfferCategory_Csp.BaseOffer_OfferId]
IF object_id(N'[Csp].[FK_Csp.BaseOfferLinks_Csp.BaseOffer_OfferId]', N'F') IS NOT NULL
    ALTER TABLE [Csp].[BaseOfferLinks] DROP CONSTRAINT [FK_Csp.BaseOfferLinks_Csp.BaseOffer_OfferId]
IF object_id(N'[Csp].[FK_Csp.BaseOfferLinkHeader_Csp.BaseOfferLinks_LinkId]', N'F') IS NOT NULL
    ALTER TABLE [Csp].[BaseOfferLinkHeader] DROP CONSTRAINT [FK_Csp.BaseOfferLinkHeader_Csp.BaseOfferLinks_LinkId]
IF object_id(N'[Csp].[FK_Csp.BaseOfferProduct_Csp.BaseOffer_OfferId]', N'F') IS NOT NULL
    ALTER TABLE [Csp].[BaseOfferProduct] DROP CONSTRAINT [FK_Csp.BaseOfferProduct_Csp.BaseOffer_OfferId]
IF object_id(N'[Csp].[FK_Csp.PrerequisiteOffer_Csp.BaseOffer_OfferId]', N'F') IS NOT NULL
    ALTER TABLE [Csp].[PrerequisiteOffer] DROP CONSTRAINT [FK_Csp.PrerequisiteOffer_Csp.BaseOffer_OfferId]
IF object_id(N'[Csp].[FK_Csp.UpgradeTargetOffer_Csp.BaseOffer_OfferId]', N'F') IS NOT NULL
    ALTER TABLE [Csp].[UpgradeTargetOffer] DROP CONSTRAINT [FK_Csp.UpgradeTargetOffer_Csp.BaseOffer_OfferId]
IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_OfferId' AND object_id = object_id(N'[Csp].[BaseOfferCategory]', N'U'))
    DROP INDEX [IX_OfferId] ON [Csp].[BaseOfferCategory]
IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_LinkId' AND object_id = object_id(N'[Csp].[BaseOfferLinkHeader]', N'U'))
    DROP INDEX [IX_LinkId] ON [Csp].[BaseOfferLinkHeader]
IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_OfferId' AND object_id = object_id(N'[Csp].[BaseOfferLinks]', N'U'))
    DROP INDEX [IX_OfferId] ON [Csp].[BaseOfferLinks]
IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_OfferId' AND object_id = object_id(N'[Csp].[BaseOfferProduct]', N'U'))
    DROP INDEX [IX_OfferId] ON [Csp].[BaseOfferProduct]
IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_OfferId' AND object_id = object_id(N'[Csp].[PrerequisiteOffer]', N'U'))
    DROP INDEX [IX_OfferId] ON [Csp].[PrerequisiteOffer]
IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_OfferId' AND object_id = object_id(N'[Csp].[UpgradeTargetOffer]', N'U'))
    DROP INDEX [IX_OfferId] ON [Csp].[UpgradeTargetOffer]
DROP TABLE [Csp].[BaseOffer]
DROP TABLE [Csp].[BaseOfferCategory]
DROP TABLE [Csp].[BaseOfferLinkHeader]
DROP TABLE [Csp].[BaseOfferLinks]
DROP TABLE [Csp].[BaseOfferProduct]
DROP TABLE [Csp].[PrerequisiteOffer]
DROP TABLE [Csp].[UpgradeTargetOffer]
DELETE [dbo].[__MigrationHistory]
WHERE (([MigrationId] = N'201708031446253_AddCSPOfferAndOfferRealtedTables') AND ([ContextKey] = N'CloudAssert.WAP.Billing.Persistence.Sql.Migrations.Configuration'))
DROP TABLE [Csp].[EventsLog]
DELETE [dbo].[__MigrationHistory]
WHERE (([MigrationId] = N'201708031411428_AddCSPEventsLogTable') AND ([ContextKey] = N'CloudAssert.WAP.Billing.Persistence.Sql.Migrations.Configuration'))
DECLARE @var2 nvarchar(128)
SELECT @var2 = name
FROM sys.default_constraints
WHERE parent_object_id = object_id(N'dbo.BillingMonthlyRunningSummary')
AND col_name(parent_object_id, parent_column_id) = 'MarkupPercent';
IF @var2 IS NOT NULL
    EXECUTE('ALTER TABLE [dbo].[BillingMonthlyRunningSummary] DROP CONSTRAINT [' + @var2 + ']')
ALTER TABLE [dbo].[BillingMonthlyRunningSummary] DROP COLUMN [MarkupPercent]
DECLARE @var3 nvarchar(128)
SELECT @var3 = name
FROM sys.default_constraints
WHERE parent_object_id = object_id(N'dbo.BillingMonthlyRunningSummary')
AND col_name(parent_object_id, parent_column_id) = 'PricingProfileHierarchy';
IF @var3 IS NOT NULL
    EXECUTE('ALTER TABLE [dbo].[BillingMonthlyRunningSummary] DROP CONSTRAINT [' + @var3 + ']')
ALTER TABLE [dbo].[BillingMonthlyRunningSummary] DROP COLUMN [PricingProfileHierarchy]
DECLARE @var4 nvarchar(128)
SELECT @var4 = name
FROM sys.default_constraints
WHERE parent_object_id = object_id(N'dbo.BillingMonthlyResourceSummaries')
AND col_name(parent_object_id, parent_column_id) = 'MarkupPercent';
IF @var4 IS NOT NULL
    EXECUTE('ALTER TABLE [dbo].[BillingMonthlyResourceSummaries] DROP CONSTRAINT [' + @var4 + ']')
ALTER TABLE [dbo].[BillingMonthlyResourceSummaries] DROP COLUMN [MarkupPercent]
DECLARE @var5 nvarchar(128)
SELECT @var5 = name
FROM sys.default_constraints
WHERE parent_object_id = object_id(N'dbo.BillingMonthlyResourceSummaries')
AND col_name(parent_object_id, parent_column_id) = 'PricingProfileHierarchy';
IF @var5 IS NOT NULL
    EXECUTE('ALTER TABLE [dbo].[BillingMonthlyResourceSummaries] DROP CONSTRAINT [' + @var5 + ']')
ALTER TABLE [dbo].[BillingMonthlyResourceSummaries] DROP COLUMN [PricingProfileHierarchy]
DECLARE @var6 nvarchar(128)
SELECT @var6 = name
FROM sys.default_constraints
WHERE parent_object_id = object_id(N'dbo.BillingMonthlyRecords')
AND col_name(parent_object_id, parent_column_id) = 'MarkupPercent';
IF @var6 IS NOT NULL
    EXECUTE('ALTER TABLE [dbo].[BillingMonthlyRecords] DROP CONSTRAINT [' + @var6 + ']')
ALTER TABLE [dbo].[BillingMonthlyRecords] DROP COLUMN [MarkupPercent]
DECLARE @var7 nvarchar(128)
SELECT @var7 = name
FROM sys.default_constraints
WHERE parent_object_id = object_id(N'dbo.BillingMonthlyRecords')
AND col_name(parent_object_id, parent_column_id) = 'PricingProfileHierarchy';
IF @var7 IS NOT NULL
    EXECUTE('ALTER TABLE [dbo].[BillingMonthlyRecords] DROP CONSTRAINT [' + @var7 + ']')
ALTER TABLE [dbo].[BillingMonthlyRecords] DROP COLUMN [PricingProfileHierarchy]
DECLARE @var8 nvarchar(128)
SELECT @var8 = name
FROM sys.default_constraints
WHERE parent_object_id = object_id(N'dbo.BillingHourlyRecords')
AND col_name(parent_object_id, parent_column_id) = 'MarkupPercent';
IF @var8 IS NOT NULL
    EXECUTE('ALTER TABLE [dbo].[BillingHourlyRecords] DROP CONSTRAINT [' + @var8 + ']')
ALTER TABLE [dbo].[BillingHourlyRecords] DROP COLUMN [MarkupPercent]
DECLARE @var9 nvarchar(128)
SELECT @var9 = name
FROM sys.default_constraints
WHERE parent_object_id = object_id(N'dbo.BillingHourlyRecords')
AND col_name(parent_object_id, parent_column_id) = 'PricingProfileHierarchy';
IF @var9 IS NOT NULL
    EXECUTE('ALTER TABLE [dbo].[BillingHourlyRecords] DROP CONSTRAINT [' + @var9 + ']')
ALTER TABLE [dbo].[BillingHourlyRecords] DROP COLUMN [PricingProfileHierarchy]
DECLARE @var10 nvarchar(128)
SELECT @var10 = name
FROM sys.default_constraints
WHERE parent_object_id = object_id(N'dbo.BillingDailyRecords')
AND col_name(parent_object_id, parent_column_id) = 'MarkupPercent';
IF @var10 IS NOT NULL
    EXECUTE('ALTER TABLE [dbo].[BillingDailyRecords] DROP CONSTRAINT [' + @var10 + ']')
ALTER TABLE [dbo].[BillingDailyRecords] DROP COLUMN [MarkupPercent]
DECLARE @var11 nvarchar(128)
SELECT @var11 = name
FROM sys.default_constraints
WHERE parent_object_id = object_id(N'dbo.BillingDailyRecords')
AND col_name(parent_object_id, parent_column_id) = 'PricingProfileHierarchy';
IF @var11 IS NOT NULL
    EXECUTE('ALTER TABLE [dbo].[BillingDailyRecords] DROP CONSTRAINT [' + @var11 + ']')
ALTER TABLE [dbo].[BillingDailyRecords] DROP COLUMN [PricingProfileHierarchy]
DECLARE @var12 nvarchar(128)
SELECT @var12 = name
FROM sys.default_constraints
WHERE parent_object_id = object_id(N'dbo.PricingProfiles')
AND col_name(parent_object_id, parent_column_id) = 'IsAggregateBaseMarkups';
IF @var12 IS NOT NULL
    EXECUTE('ALTER TABLE [dbo].[PricingProfiles] DROP CONSTRAINT [' + @var12 + ']')
ALTER TABLE [dbo].[PricingProfiles] DROP COLUMN [IsAggregateBaseMarkups]
DECLARE @var13 nvarchar(128)
SELECT @var13 = name
FROM sys.default_constraints
WHERE parent_object_id = object_id(N'dbo.PricingProfiles')
AND col_name(parent_object_id, parent_column_id) = 'ApplyMarkupOnlyToBasePP';
IF @var13 IS NOT NULL
    EXECUTE('ALTER TABLE [dbo].[PricingProfiles] DROP CONSTRAINT [' + @var13 + ']')
ALTER TABLE [dbo].[PricingProfiles] DROP COLUMN [ApplyMarkupOnlyToBasePP]
DECLARE @var14 nvarchar(128)
SELECT @var14 = name
FROM sys.default_constraints
WHERE parent_object_id = object_id(N'dbo.PricingProfiles')
AND col_name(parent_object_id, parent_column_id) = 'MarkupPercent';
IF @var14 IS NOT NULL
    EXECUTE('ALTER TABLE [dbo].[PricingProfiles] DROP CONSTRAINT [' + @var14 + ']')
ALTER TABLE [dbo].[PricingProfiles] DROP COLUMN [MarkupPercent]
DELETE [dbo].[__MigrationHistory]
WHERE (([MigrationId] = N'201707281238543_AddMarkupPricingProfileAndBillingTables') AND ([ContextKey] = N'CloudAssert.WAP.Billing.Persistence.Sql.Migrations.Configuration'))
DECLARE @var15 nvarchar(128)
SELECT @var15 = name
FROM sys.default_constraints
WHERE parent_object_id = object_id(N'Csp.CustomerProfile')
AND col_name(parent_object_id, parent_column_id) = 'SignupStateString';
IF @var15 IS NOT NULL
    EXECUTE('ALTER TABLE [Csp].[CustomerProfile] DROP CONSTRAINT [' + @var15 + ']')
ALTER TABLE [Csp].[CustomerProfile] DROP COLUMN [SignupStateString]
DECLARE @var16 nvarchar(128)
SELECT @var16 = name
FROM sys.default_constraints
WHERE parent_object_id = object_id(N'Csp.CustomerProfile')
AND col_name(parent_object_id, parent_column_id) = 'SignupState';
IF @var16 IS NOT NULL
    EXECUTE('ALTER TABLE [Csp].[CustomerProfile] DROP CONSTRAINT [' + @var16 + ']')
ALTER TABLE [Csp].[CustomerProfile] DROP COLUMN [SignupState]
DELETE [dbo].[__MigrationHistory]
WHERE (([MigrationId] = N'201707280738438_AddCspSingnUpStatusColumnInCustomerProfiletable') AND ([ContextKey] = N'CloudAssert.WAP.Billing.Persistence.Sql.Migrations.Configuration'))
