ALTER TABLE [dbo].[Script] DROP CONSTRAINT [Script_RunExclusively_DF];


GO
ALTER TABLE [dbo].[Script] DROP CONSTRAINT [Script_UseLegacyDeploymentPath_DF];


GO
ALTER TABLE [dbo].[Script] DROP CONSTRAINT [ScriptIDFromSC_DF];


GO
ALTER TABLE [dbo].[Script] DROP CONSTRAINT [ScriptRevisionGuid_Script_DF];


GO
ALTER TABLE [dbo].[AutomatedTaskFileLastUsed] DROP CONSTRAINT [AutomatedTaskFileLastUsed_AutomatedTaskParameterFile_FK];


GO
ALTER TABLE [dbo].[AutomatedTaskPackageScriptParameter] DROP CONSTRAINT [FK_AutomatedTaskPacakageScriptParameter_AutomatedTaskParameterFile];


GO
ALTER TABLE [dbo].[AutomatedTaskParameter] DROP CONSTRAINT [AutomatedTaskParameter_AutomatedTaskParameterFileID_FK];


GO
ALTER TABLE [dbo].[DeviceRegistryInfo] DROP CONSTRAINT [DeviceRegistryInfo_DeviceGuid_Device_DeviceGuid_FK];


GO
ALTER TABLE [dbo].[Script] DROP CONSTRAINT [FK_Script_ScriptStandardOutputType];


GO
ALTER TABLE [dbo].[Script] DROP CONSTRAINT [FK_Script_ScriptType];


GO
ALTER TABLE [dbo].[AutomatedTask] DROP CONSTRAINT [FK_AutomatedTask_ScriptID_Script_ScriptID_FK];


GO
ALTER TABLE [dbo].[ScriptLastUsed] DROP CONSTRAINT [FK_ScriptLastUsed_Script];


GO
ALTER TABLE [dbo].[AutomatedTaskPackageNode] DROP CONSTRAINT [FK_AutomatedTaskPackageNonde_Script];


GO
/*
The column [dbo].[AutomatedTaskParameterFile].[FileBinaryMD5] is being dropped, data loss could occur.

The column [dbo].[AutomatedTaskParameterFile].[FileBinaryHash] on table [dbo].[AutomatedTaskParameterFile] must be added, but the column has no default value and does not allow NULL values. If the table contains data, the ALTER script will not work. To avoid this issue you must either: add a default value to the column, mark it as allowing NULL values, or enable the generation of smart-defaults as a deployment option.
*/
GO
BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

CREATE TABLE [dbo].[tmp_ms_xx_AutomatedTaskParameterFile] (
    [AutomatedTaskParameterFileID] INT            NOT NULL,
    [DateAdded]                    DATETIME       NOT NULL,
    [FileName]                     NVARCHAR (255) NOT NULL,
    [FileBinaryHash]               NVARCHAR (64)  NOT NULL,
    [FileBinarySizeInBytes]        INT            NOT NULL,
    CONSTRAINT [tmp_ms_xx_constraint_AutomatedTaskParameterFile_PK1] PRIMARY KEY CLUSTERED ([AutomatedTaskParameterFileID] ASC)
);

IF EXISTS (SELECT TOP 1 1 
           FROM   [dbo].[AutomatedTaskParameterFile])
    BEGIN
        INSERT INTO [dbo].[tmp_ms_xx_AutomatedTaskParameterFile] ([AutomatedTaskParameterFileID], [DateAdded], [FileName], [FileBinaryHash], [FileBinarySizeInBytes])
        SELECT   [AutomatedTaskParameterFileID],
                 [DateAdded],
                 [FileName],
                 [FileBinaryMD5],
                 [FileBinarySizeInBytes]
        FROM     [dbo].[AutomatedTaskParameterFile]
        ORDER BY [AutomatedTaskParameterFileID] ASC;
    END

DROP TABLE [dbo].[AutomatedTaskParameterFile];

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_AutomatedTaskParameterFile]', N'AutomatedTaskParameterFile';

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_constraint_AutomatedTaskParameterFile_PK1]', N'AutomatedTaskParameterFile_PK', N'OBJECT';

COMMIT TRANSACTION;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;


GO
BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

CREATE TABLE [dbo].[tmp_ms_xx_DeviceRegistryInfo] (
    [DeviceGuid]          UNIQUEIDENTIFIER NOT NULL,
    [InstallationType]    NVARCHAR (100)   NULL,
    [ReleaseId]           INT              NULL,
    [DisplayVersion]      NVARCHAR (10)    NULL,
    [UpdateBuildRevision] INT              NULL,
    [DateCreated]         DATETIME         NOT NULL,
    [DateModified]        DATETIME         NOT NULL,
    [OMRowVersion]        ROWVERSION       NOT NULL,
    CONSTRAINT [tmp_ms_xx_constraint_DeviceRegistryInfo_DeviceGuid_PK1] PRIMARY KEY CLUSTERED ([DeviceGuid] ASC)
);

IF EXISTS (SELECT TOP 1 1 
           FROM   [dbo].[DeviceRegistryInfo])
    BEGIN
        INSERT INTO [dbo].[tmp_ms_xx_DeviceRegistryInfo] ([DeviceGuid], [InstallationType], [ReleaseId], [UpdateBuildRevision], [DateCreated], [DateModified])
        SELECT   [DeviceGuid],
                 [InstallationType],
                 [ReleaseId],
                 [UpdateBuildRevision],
                 [DateCreated],
                 [DateModified]
        FROM     [dbo].[DeviceRegistryInfo]
        ORDER BY [DeviceGuid] ASC;
    END

DROP TABLE [dbo].[DeviceRegistryInfo];

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_DeviceRegistryInfo]', N'DeviceRegistryInfo';

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_constraint_DeviceRegistryInfo_DeviceGuid_PK1]', N'DeviceRegistryInfo_DeviceGuid_PK', N'OBJECT';

COMMIT TRANSACTION;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;


GO
/*
The column [dbo].[Script].[MD5Hash] is being dropped, data loss could occur.

The column [dbo].[Script].[FileHash] on table [dbo].[Script] must be added, but the column has no default value and does not allow NULL values. If the table contains data, the ALTER script will not work. To avoid this issue you must either: add a default value to the column, mark it as allowing NULL values, or enable the generation of smart-defaults as a deployment option.
*/
GO
BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

CREATE TABLE [dbo].[tmp_ms_xx_Script] (
    [ScriptID]                   INT              IDENTITY (1, 1) NOT NULL,
    [ScriptGuid]                 UNIQUEIDENTIFIER NOT NULL,
    [Version]                    NVARCHAR (50)    NOT NULL,
    [EntryPointFileName]         NVARCHAR (255)   NOT NULL,
    [FileName]                   NVARCHAR (255)   NOT NULL,
    [ScriptTypeID]               INT              NOT NULL,
    [ScriptStandardOutputTypeID] INT              NULL,
    [FileSize]                   INT              NOT NULL,
    [FileHash]                   NVARCHAR (255)   NOT NULL,
    [ScriptIDFromSC]             INT              CONSTRAINT [ScriptIDFromSC_DF] DEFAULT ((0)) NOT NULL,
    [EarliestMWVersionSupported] NVARCHAR (255)   NOT NULL,
    [UseLegacyDeploymentPath]    BIT              CONSTRAINT [Script_UseLegacyDeploymentPath_DF] DEFAULT ((0)) NOT NULL,
    [RunExclusively]             BIT              CONSTRAINT [Script_RunExclusively_DF] DEFAULT ((0)) NOT NULL,
    [ScriptRevisionGuid]         UNIQUEIDENTIFIER CONSTRAINT [ScriptRevisionGuid_Script_DF] DEFAULT (newid()) NOT NULL,
    [ScriptName]                 NVARCHAR (50)    NULL,
    CONSTRAINT [tmp_ms_xx_constraint_PK_Script1] PRIMARY KEY CLUSTERED ([ScriptID] ASC),
    CONSTRAINT [tmp_ms_xx_constraint_AK_Script_ScriptRevisionGuid1] UNIQUE NONCLUSTERED ([ScriptRevisionGuid] ASC)
);

IF EXISTS (SELECT TOP 1 1 
           FROM   [dbo].[Script])
    BEGIN
        SET IDENTITY_INSERT [dbo].[tmp_ms_xx_Script] ON;
        INSERT INTO [dbo].[tmp_ms_xx_Script] ([ScriptID], [ScriptGuid], [Version], [EntryPointFileName], [FileName], [ScriptTypeID], [ScriptStandardOutputTypeID], [FileSize], [FileHash], [ScriptIDFromSC], [EarliestMWVersionSupported], [UseLegacyDeploymentPath], [RunExclusively], [ScriptRevisionGuid], [ScriptName])
        SELECT   [ScriptID],
                 [ScriptGuid],
                 [Version],
                 [EntryPointFileName],
                 [FileName],
                 [ScriptTypeID],
                 [ScriptStandardOutputTypeID],
                 [FileSize],
                 [MD5Hash],
                 [ScriptIDFromSC],
                 [EarliestMWVersionSupported],
                 [UseLegacyDeploymentPath],
                 [RunExclusively],
                 [ScriptRevisionGuid],
                 [ScriptName]
        FROM     [dbo].[Script]
        ORDER BY [ScriptID] ASC;
        SET IDENTITY_INSERT [dbo].[tmp_ms_xx_Script] OFF;
    END

DROP TABLE [dbo].[Script];

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_Script]', N'Script';

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_constraint_PK_Script1]', N'PK_Script', N'OBJECT';

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_constraint_AK_Script_ScriptRevisionGuid1]', N'AK_Script_ScriptRevisionGuid', N'OBJECT';

COMMIT TRANSACTION;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;


GO
CREATE TABLE [dbo].[Asset_Win32_EncryptableVolume] (
    [Win32_EncryptableVolumeGuid] UNIQUEIDENTIFIER NOT NULL,
    [DeviceGuid]                  UNIQUEIDENTIFIER NOT NULL,
    [DeviceID]                    NVARCHAR (1000)  NULL,
    [PersistentVolumeID]          NVARCHAR (1000)  NULL,
    [DriveLetter]                 NVARCHAR (1000)  NULL,
    [ProtectionStatus]            INT              NULL,
    [ConversionStatus]            INT              NULL,
    [DateCreated]                 DATETIME         NOT NULL,
    [DateModified]                DATETIME         NOT NULL,
    [OMRowVersion]                ROWVERSION       NOT NULL,
    CONSTRAINT [Asset_Win32_EncryptableVolume_Win32_EncryptableVolumeGuid_PK] PRIMARY KEY NONCLUSTERED ([Win32_EncryptableVolumeGuid] ASC)
);


GO
CREATE UNIQUE CLUSTERED INDEX [Asset_Win32_EncryptableVolume_Win32_EncryptableVolumeGuid_UX]
    ON [dbo].[Asset_Win32_EncryptableVolume]([Win32_EncryptableVolumeGuid] ASC, [DeviceGuid] ASC);


GO
CREATE TABLE [dbo].[Asset_Win32_Tpm] (
    [Win32_TpmGuid]               UNIQUEIDENTIFIER NOT NULL,
    [DeviceGuid]                  UNIQUEIDENTIFIER NOT NULL,
    [IsActivated]                 BIT              NULL,
    [IsEnabled]                   BIT              NULL,
    [IsOwned]                     BIT              NULL,
    [SpecVersion]                 NVARCHAR (500)   NULL,
    [ManufacturerVersion]         NVARCHAR (500)   NULL,
    [ManufacturerVersionInfo]     NVARCHAR (500)   NULL,
    [ManufacturerId]              BIGINT           NULL,
    [PhysicalPresenceVersionInfo] NVARCHAR (500)   NULL,
    [DateCreated]                 DATETIME         NOT NULL,
    [DateModified]                DATETIME         NOT NULL,
    [OMRowVersion]                ROWVERSION       NOT NULL,
    CONSTRAINT [Asset_Win32_Tpm_Win32_TpmGuid_PK] PRIMARY KEY NONCLUSTERED ([Win32_TpmGuid] ASC)
);


GO
CREATE CLUSTERED INDEX [Asset_Win32_Tpm_Win32_DeviceGuid_CLUST_IX]
    ON [dbo].[Asset_Win32_Tpm]([DeviceGuid] ASC);


GO
CREATE UNIQUE NONCLUSTERED INDEX [Asset_Win32_Tpm_Win32_TpmGuid_i_DeviceGuid_OMRowVersion_UX]
    ON [dbo].[Asset_Win32_Tpm]([Win32_TpmGuid] ASC)
    INCLUDE([DeviceGuid], [OMRowVersion]);


GO
CREATE TABLE [dbo].[BitLockerSettings] (
    [DeviceGuid]      UNIQUEIDENTIFIER NOT NULL,
    [IsEnabled]       BIT              NOT NULL,
    [RecoveryKey]     NVARCHAR (100)   NULL,
    [EncryptUsedOnly] BIT              NOT NULL,
    [GenerateKey]     BIT              NOT NULL,
    [DriveLetter]     NCHAR (1)        NOT NULL,
    [SiteName]        NVARCHAR (255)   NOT NULL,
    [DeviceName]      NVARCHAR (255)   NOT NULL,
    [Modified]        DATETIME         NOT NULL,
    [IVector]         NVARCHAR (100)   NULL,
    CONSTRAINT [PK_BitLockerSettings] PRIMARY KEY CLUSTERED ([DeviceGuid] ASC)
);


GO
ALTER TABLE [dbo].[BitLockerSettings]
    ADD CONSTRAINT [BitLockerSettings_IsEnabled_DF] DEFAULT ((0)) FOR [IsEnabled];


GO
ALTER TABLE [dbo].[BitLockerSettings]
    ADD CONSTRAINT [BitLockerSettings_EncryptUsedOnly_DF] DEFAULT ((1)) FOR [EncryptUsedOnly];


GO
ALTER TABLE [dbo].[BitLockerSettings]
    ADD CONSTRAINT [BitLockerSettings_GenerateKey_DF] DEFAULT ((1)) FOR [GenerateKey];


GO
ALTER TABLE [dbo].[AutomatedTaskFileLastUsed] WITH NOCHECK
    ADD CONSTRAINT [AutomatedTaskFileLastUsed_AutomatedTaskParameterFile_FK] FOREIGN KEY ([AutomatedTaskParameterFileID]) REFERENCES [dbo].[AutomatedTaskParameterFile] ([AutomatedTaskParameterFileID]);


GO
ALTER TABLE [dbo].[AutomatedTaskPackageScriptParameter] WITH NOCHECK
    ADD CONSTRAINT [FK_AutomatedTaskPacakageScriptParameter_AutomatedTaskParameterFile] FOREIGN KEY ([AutomatedTaskParameterFileID]) REFERENCES [dbo].[AutomatedTaskParameterFile] ([AutomatedTaskParameterFileID]);


GO
ALTER TABLE [dbo].[AutomatedTaskParameter] WITH NOCHECK
    ADD CONSTRAINT [AutomatedTaskParameter_AutomatedTaskParameterFileID_FK] FOREIGN KEY ([AutomatedTaskParameterFileID]) REFERENCES [dbo].[AutomatedTaskParameterFile] ([AutomatedTaskParameterFileID]);


GO
ALTER TABLE [dbo].[DeviceRegistryInfo] WITH NOCHECK
    ADD CONSTRAINT [DeviceRegistryInfo_DeviceGuid_Device_DeviceGuid_FK] FOREIGN KEY ([DeviceGuid]) REFERENCES [dbo].[Device] ([DeviceGuid]);


GO
ALTER TABLE [dbo].[Script] WITH NOCHECK
    ADD CONSTRAINT [FK_Script_ScriptStandardOutputType] FOREIGN KEY ([ScriptStandardOutputTypeID]) REFERENCES [dbo].[ScriptStandardOutputType] ([ScriptStandardOutputTypeID]);


GO
ALTER TABLE [dbo].[Script] WITH NOCHECK
    ADD CONSTRAINT [FK_Script_ScriptType] FOREIGN KEY ([ScriptTypeID]) REFERENCES [dbo].[ScriptType] ([ScriptTypeID]);


GO
ALTER TABLE [dbo].[AutomatedTask] WITH NOCHECK
    ADD CONSTRAINT [FK_AutomatedTask_ScriptID_Script_ScriptID_FK] FOREIGN KEY ([ScriptID]) REFERENCES [dbo].[Script] ([ScriptID]);


GO
ALTER TABLE [dbo].[ScriptLastUsed] WITH NOCHECK
    ADD CONSTRAINT [FK_ScriptLastUsed_Script] FOREIGN KEY ([ScriptID]) REFERENCES [dbo].[Script] ([ScriptID]);


GO
ALTER TABLE [dbo].[AutomatedTaskPackageNode] WITH NOCHECK
    ADD CONSTRAINT [FK_AutomatedTaskPackageNonde_Script] FOREIGN KEY ([ScriptID]) REFERENCES [dbo].[Script] ([ScriptID]);


GO
ALTER TABLE [dbo].[Asset_Win32_EncryptableVolume] WITH NOCHECK
    ADD CONSTRAINT [Asset_Win32_EncryptableVolume_Device_FK] FOREIGN KEY ([DeviceGuid]) REFERENCES [dbo].[Device] ([DeviceGuid]);


GO
ALTER TABLE [dbo].[Asset_Win32_Tpm] WITH NOCHECK
    ADD CONSTRAINT [Asset_Win32_Tpm_Device_FK] FOREIGN KEY ([DeviceGuid]) REFERENCES [dbo].[Device] ([DeviceGuid]);


GO
ALTER TABLE [dbo].[AutomatedTaskFileLastUsed] WITH CHECK CHECK CONSTRAINT [AutomatedTaskFileLastUsed_AutomatedTaskParameterFile_FK];

ALTER TABLE [dbo].[AutomatedTaskPackageScriptParameter] WITH CHECK CHECK CONSTRAINT [FK_AutomatedTaskPacakageScriptParameter_AutomatedTaskParameterFile];

ALTER TABLE [dbo].[AutomatedTaskParameter] WITH CHECK CHECK CONSTRAINT [AutomatedTaskParameter_AutomatedTaskParameterFileID_FK];

ALTER TABLE [dbo].[DeviceRegistryInfo] WITH CHECK CHECK CONSTRAINT [DeviceRegistryInfo_DeviceGuid_Device_DeviceGuid_FK];

ALTER TABLE [dbo].[Script] WITH CHECK CHECK CONSTRAINT [FK_Script_ScriptStandardOutputType];

ALTER TABLE [dbo].[Script] WITH CHECK CHECK CONSTRAINT [FK_Script_ScriptType];

ALTER TABLE [dbo].[AutomatedTask] WITH CHECK CHECK CONSTRAINT [FK_AutomatedTask_ScriptID_Script_ScriptID_FK];

ALTER TABLE [dbo].[ScriptLastUsed] WITH CHECK CHECK CONSTRAINT [FK_ScriptLastUsed_Script];

ALTER TABLE [dbo].[AutomatedTaskPackageNode] WITH CHECK CHECK CONSTRAINT [FK_AutomatedTaskPackageNonde_Script];

ALTER TABLE [dbo].[Asset_Win32_EncryptableVolume] WITH CHECK CHECK CONSTRAINT [Asset_Win32_EncryptableVolume_Device_FK];

ALTER TABLE [dbo].[Asset_Win32_Tpm] WITH CHECK CHECK CONSTRAINT [Asset_Win32_Tpm_Device_FK];


GO
