CREATE TABLE [Asset_SMBios_MemoryDevice] (
  [SMBios_MemoryDeviceGuid] uniqueidentifier NOT NULL
, [DeviceGuid] uniqueidentifier NOT NULL
, [AssetTag] nvarchar(100) NULL
, [Attributes] tinyint NULL
, [BankLocator] nvarchar(100) NULL
, [DataWidth] int NULL
, [DeviceLocator] nvarchar(100) NULL
, [DevSet] tinyint NULL
, [Manufacturer] nvarchar(100) NULL
, [MemType] tinyint NULL
, [PartNumber] nvarchar(100) NULL
, [SerialNumber] nvarchar(100) NULL
, [Size] int NULL
, [Speed] int NULL
, [TotalWidth] int NULL
, [TypeDetails] int NULL
, [DateCreated] datetime NOT NULL
, [DateModified] datetime NOT NULL
, [OMRowVersion] rowversion NOT NULL
);
GO
CREATE TABLE [Asset_SMBios_Processor] (
  [SMBios_ProcessorGuid] uniqueidentifier NOT NULL
, [DeviceGuid] uniqueidentifier NOT NULL
, [AssetTag] nvarchar(100) NULL
, [CoreCount] tinyint NULL
, [CoreEnabled] tinyint NULL
, [CpuNumber] int NULL
, [CurrentSpeed] int NULL
, [ExternalClock] int NULL
, [Manufacturer] nvarchar(100) NULL
, [MaxSpeed] int NULL
, [ProcCharacteristics] int NULL
, [ProcessorFamily] tinyint NULL
, [ProcessorID] bigint NULL
, [ProcessorType] tinyint NULL
, [ProcUpgrade] tinyint NULL
, [SerialNumber] nvarchar(100) NULL
, [SocketDesignation] nvarchar(100) NULL
, [Status] tinyint NULL
, [ThreadCount] tinyint NULL
, [Version] nvarchar(100) NULL
, [Voltage] tinyint NULL
, [DateCreated] datetime NOT NULL
, [DateModified] datetime NOT NULL
, [OMRowVersion] rowversion NOT NULL
);
GO
CREATE TABLE [Asset_Win32_Environment] (
  [Win32_EnvironmentGuid] uniqueidentifier NOT NULL
, [DeviceGuid] uniqueidentifier NOT NULL
, [Caption] nvarchar(500) NULL
, [Description] nvarchar(2000) NULL
, [InstallDate] datetime NULL
, [Status] nvarchar(1000) NULL
, [Name] nvarchar(1000) NULL
, [SystemVariable] bit NULL
, [UserName] nvarchar(1000) NULL
, [VariableValue] nvarchar(3000) NULL
, [DateCreated] datetime NOT NULL
, [DateModified] datetime NOT NULL
, [OMRowVersion] rowversion NOT NULL
);
GO
CREATE TABLE [Asset_Win32_Printer] (
  [Win32_PrinterGuid] uniqueidentifier NOT NULL
, [DeviceGuid] uniqueidentifier NOT NULL
, [Attributes] bigint NULL
, [Availability] int NULL
, [AvailableJobSheets] nvarchar(3000) NULL
, [AveragePagesPerMinute] bigint NULL
, [Capabilities] nvarchar(3000) NULL
, [CapabilityDescriptions] nvarchar(3000) NULL
, [Caption] nvarchar(500) NULL
, [CharSetsSupported] nvarchar(3000) NULL
, [Comment] nvarchar(1000) NULL
, [ConfigManagerErrorCode] bigint NULL
, [ConfigManagerUserConfig] bit NULL
, [CreationClassName] nvarchar(1000) NULL
, [CurrentCapabilities] nvarchar(3000) NULL
, [CurrentCharSet] nvarchar(1000) NULL
, [CurrentLanguage] int NULL
, [CurrentMimeType] nvarchar(1000) NULL
, [CurrentNaturalLanguage] nvarchar(1000) NULL
, [CurrentPaperType] nvarchar(1000) NULL
, [Default] bit NULL
, [DefaultCapabilities] nvarchar(3000) NULL
, [DefaultCopies] bigint NULL
, [DefaultLanguage] int NULL
, [DefaultMimeType] nvarchar(1000) NULL
, [DefaultNumberUp] bigint NULL
, [DefaultPaperType] nvarchar(1000) NULL
, [DefaultPriority] bigint NULL
, [Description] nvarchar(2000) NULL
, [DetectedErrorState] int NULL
, [DeviceID] nvarchar(1000) NULL
, [Direct] bit NULL
, [DoCompleteFirst] bit NULL
, [DriverName] nvarchar(1000) NULL
, [EnableBIDI] bit NULL
, [EnableDevQueryPrint] bit NULL
, [ErrorCleared] bit NULL
, [ErrorDescription] nvarchar(2000) NULL
, [ErrorInformation] nvarchar(3000) NULL
, [ExtendedDetectedErrorState] int NULL
, [ExtendedPrinterStatus] int NULL
, [Hidden] bit NULL
, [HorizontalResolution] bigint NULL
, [InstallDate] datetime NULL
, [JobCountSinceLastReset] bigint NULL
, [KeepPrintedJobs] bit NULL
, [LanguagesSupported] nvarchar(3000) NULL
, [LastErrorCode] bigint NULL
, [Local] bit NULL
, [Location] nvarchar(1000) NULL
, [MarkingTechnology] int NULL
, [MaxCopies] bigint NULL
, [MaxNumberUp] bigint NULL
, [MaxSizeSupported] bigint NULL
, [MimeTypesSupported] nvarchar(3000) NULL
, [Name] nvarchar(1000) NULL
, [NaturalLanguagesSupported] nvarchar(3000) NULL
, [Network] bit NULL
, [PaperSizesSupported] nvarchar(3000) NULL
, [PaperTypesAvailable] nvarchar(3000) NULL
, [Parameters] nvarchar(1000) NULL
, [PNPDeviceID] nvarchar(1000) NULL
, [PortName] nvarchar(1000) NULL
, [PowerManagementCapabilities] nvarchar(3000) NULL
, [PowerManagementSupported] bit NULL
, [PrinterPaperNames] nvarchar(1000) NULL
, [PrinterState] bigint NULL
, [PrinterStatus] int NULL
, [PrintJobDataType] nvarchar(1000) NULL
, [PrintProcessor] nvarchar(1000) NULL
, [Priority] bigint NULL
, [Published] bit NULL
, [Queued] bit NULL
, [RawOnly] bit NULL
, [SeparatorFile] nvarchar(1000) NULL
, [ServerName] nvarchar(1000) NULL
, [Shared] bit NULL
, [ShareName] nvarchar(1000) NULL
, [SpoolEnabled] bit NULL
, [StartTime] datetime NULL
, [Status] nvarchar(1000) NULL
, [StatusInfo] int NULL
, [SystemCreationClassName] nvarchar(1000) NULL
, [SystemName] nvarchar(1000) NULL
, [TimeOfLastReset] datetime NULL
, [UntilTime] datetime NULL
, [VerticalResolution] bigint NULL
, [WorkOffline] bit NULL
, [DateCreated] datetime NOT NULL
, [DateModified] datetime NOT NULL
, [OMRowVersion] rowversion NOT NULL
);
GO
CREATE TABLE [IBUDeploymentStatus] (
  [DeploymentId] int NOT NULL
, [DeviceGuid] uniqueidentifier NOT NULL
, [CountryCode] nvarchar(4) NOT NULL
, [IBUCustomerUsername] nvarchar(255) NOT NULL
, [IBUCustomerPassword] ntext NOT NULL
, [ComputerId] nchar(4) NOT NULL
, [Status] tinyint NOT NULL
, [StatusDetails] ntext NULL
, [DateCreated] datetime NOT NULL
, [LastUpdate] datetime NULL
);
GO
ALTER TABLE [Asset_SMBios_MemoryDevice] ADD CONSTRAINT [Asset_SMBios_MemoryDevice_SMBios_MemoryDeviceGuid_PK] PRIMARY KEY ([SMBios_MemoryDeviceGuid]);
GO
ALTER TABLE [Asset_SMBios_Processor] ADD CONSTRAINT [Asset_SMBios_Processor_SMBios_Processor_PK] PRIMARY KEY ([SMBios_ProcessorGuid]);
GO
ALTER TABLE [Asset_Win32_Environment] ADD CONSTRAINT [Asset_Win32_Environment_Win32_EnvironmentGuid_PK] PRIMARY KEY ([Win32_EnvironmentGuid]);
GO
ALTER TABLE [Asset_Win32_Printer] ADD CONSTRAINT [Asset_Win32_Printer_Win32_PrinterGuid_PK] PRIMARY KEY ([Win32_PrinterGuid]);
GO
ALTER TABLE [IBUDeploymentStatus] ADD CONSTRAINT [PK_IBUDeploymentStatus] PRIMARY KEY ([DeploymentId]);
GO

ALTER TABLE [IBUDeploymentStatus] ADD CONSTRAINT [FK_IBUDeploymentStatus_Device] FOREIGN KEY ([DeviceGuid])
REFERENCES [Device]([DeviceGuid]) ON DELETE CASCADE ON UPDATE CASCADE ;
GO

CREATE INDEX [Asset_SMBios_MemoryDevice_DeviceGuid_IX] ON [Asset_SMBios_MemoryDevice] ([DeviceGuid] ASC);
GO
ALTER TABLE [Asset_SMBios_MemoryDevice] ADD CONSTRAINT [Asset_SMBios_MemoryDevice_SMBios_MemoryDeviceGuid_UX] UNIQUE ([SMBios_MemoryDeviceGuid]);
GO
CREATE INDEX [Asset_SMBios_Processor_DeviceGuid_IX] ON [Asset_SMBios_Processor] ([DeviceGuid] ASC);
GO
ALTER TABLE [Asset_SMBios_Processor] ADD CONSTRAINT [Asset_SMBios_Processor_SMBios_ProcessorGuid_UX] UNIQUE ([SMBios_ProcessorGuid]);
GO
CREATE INDEX [Asset_Win32_Environment_DeviceGuid_IX] ON [Asset_Win32_Environment] ([DeviceGuid] ASC);
GO
ALTER TABLE [Asset_Win32_Environment] ADD CONSTRAINT [Asset_Win32_Environment_Win32_EnvironmentGuid_UX] UNIQUE ([Win32_EnvironmentGuid]);
GO
CREATE INDEX [Asset_Win32_Printer_DeviceGuid_IX] ON [Asset_Win32_Printer] ([DeviceGuid] ASC);
GO
ALTER TABLE [Asset_Win32_Printer] ADD CONSTRAINT [Asset_Win32_Printer_Win32_PrinterGuid_UX] UNIQUE ([Win32_PrinterGuid]);
GO
