
CREATE TABLE [Asset_BitlockerRecoveryKey] (
  [BitlockerRecoveryKeyGuid] uniqueidentifier NOT NULL
, [DeviceGuid] uniqueidentifier NOT NULL
, [DriveLetter] nvarchar(1000) NULL
, [BitlockerRecoveryKey] nvarchar(1000) NULL
, [DateCreated] datetime NOT NULL
, [DateModified] datetime NOT NULL
, [OMRowVersion] rowversion NOT NULL
);
GO
ALTER TABLE [Asset_BitlockerRecoveryKey] ADD CONSTRAINT [Asset_BitlockerRecoveryKey_BitlockerRecoveryKey_PK] PRIMARY KEY ([BitlockerRecoveryKeyGuid]);
GO
ALTER TABLE [Asset_BitlockerRecoveryKey] ADD CONSTRAINT [Asset_BitlockerRecoveryKey_BitlockerRecoveryKeyGuid_UX] UNIQUE ([BitlockerRecoveryKeyGuid]);
GO
CREATE INDEX [Asset_BitlockerRecoveryKey_DeviceGuid_IX] ON [Asset_BitlockerRecoveryKey] ([DeviceGuid] ASC);
GO
ALTER TABLE [ASM_IVNT_BulletinApproval] ADD [OSType] tinyint NULL;
GO
UPDATE [ASM_IVNT_BulletinApproval] SET [OSType] = 1;
GO
ALTER TABLE [ASM_IVNT_BulletinApproval] ALTER COLUMN [OSType] tinyint NOT NULL;
GO
ALTER TABLE [ASM_IVNT_BulletinApproval] DROP CONSTRAINT [PK_ASM_IVNT_BulletinApproval];
GO
ALTER TABLE [ASM_IVNT_BulletinApproval] ADD CONSTRAINT [PK_ASM_IVNT_BulletinApproval] PRIMARY KEY ([ApprovalGroupGuid],[BulletinID],[OSType]);
GO
ALTER TABLE [ASM_IVNT_Device] ADD [PatchToolsInstallReturnCode] int NULL;
GO
ALTER TABLE [ASM_IVNT_PatchDeployResult] ADD [BulletinID] nvarchar(36) NULL;
GO
ALTER TABLE [ASM_IVNT_PatchNow] ADD [OSType] tinyint NULL;
GO
UPDATE [ASM_IVNT_PatchNow] SET [OSType] = 1;
GO
ALTER TABLE [ASM_IVNT_PatchNow] ALTER COLUMN [OSType] tinyint NOT NULL;
GO
ALTER TABLE [ASM_IVNT_PatchNow] DROP CONSTRAINT [PK_ASM_IVNT_PatchNow];
GO
ALTER TABLE [ASM_IVNT_PatchNow] ADD CONSTRAINT [PK_ASM_IVNT_PatchNow] PRIMARY KEY ([DeviceID],[BulletinID],[OSType]);
GO

ALTER TABLE [ASM_IVNT_PatchScanResult] ADD [BulletinID] nvarchar(36) NULL;
GO
UPDATE [ASM_IVNT_PatchScanResult] SET [BulletinID] = 'unknown';
GO
ALTER TABLE [ASM_IVNT_PatchScanResult] ALTER COLUMN [BulletinID] nvarchar(36) NOT NULL;
GO
ALTER TABLE [ASM_IVNT_PatchStatus] ADD [BulletinID] nvarchar(36) NULL;
GO
UPDATE [ASM_IVNT_PatchStatus] SET [BulletinID] = 'unknown';
GO
ALTER TABLE [ASM_IVNT_PatchStatus] ALTER COLUMN [BulletinID] nvarchar(36) NOT NULL;
GO
ALTER TABLE [BitLockerSettings] DROP CONSTRAINT [PK_BitLockerSettings];
GO
ALTER TABLE [BitLockerSettings] ADD CONSTRAINT [PK_BitLockerSettings] PRIMARY KEY ([DeviceGuid],[DriveLetter]);
GO