Tables [dbo].[Device]
PropertyValue
CollationSQL_Latin1_General_CP1_CI_AS
Row Count (~)0
Created1:20:02 AM Thursday, March 21, 2019
Last Modified1:20:39 AM Thursday, March 21, 2019
KeyNameData TypeMax Length (Bytes)Allow NullsIdentityDefault
Cluster Primary Key PK_Device_DeviceID: DeviceIDDeviceIDint4
False
1 - 1
CreateDatedatetime8
False
(getdate())
EditedFieldsvarchar(512)512
True
Foreign Keys FK_Device_AccountID_Account_AccountID: [dbo].[Account].AccountIDAccountIDint4
True
Manufacturervarchar(100)100
True
Modelvarchar(100)100
True
SerialNrvarchar(100)100
True
PrinterAssetNrvarchar(100)100
True
ERPEquipIDvarchar(1024)1024
True
ERPDatavarchar(max)max
True
Locationnvarchar(250)500
True
Descriptionvarchar(250)250
True
MacAddressvarchar(64)64
True
PrinterIPAddressvarchar(64)64
True
LocalIDnvarchar(100)200
True
Colorbit1
True
Networkedbit1
True
DeviceTypevarchar(100)100
True
Segmentvarchar(100)100
True
MonthlyDutyCyclefloat8
True
Vendorvarchar(250)250
True
SalesRepvarchar(250)250
True
PurchaseDatedatetime8
True
PurchasePricefloat8
True
PONumbervarchar(100)100
True
InstallDatedatetime8
True
InstallMeterint4
True
FirstAuditDatedatetime8
True
TotalServiceCopiesint4
True
TotalServiceCallsint4
True
LastServiceDatedatetime8
True
ServiceHistoryvarchar(255)255
True
Contactvarchar(255)255
True
OnContractbit1
True
ContractIDvarchar(512)512
True
ContractVendorvarchar(250)250
True
ContractTypevarchar(512)512
True
ContractRatefloat8
True
ContractEndDatedatetime8
True
MonthlyBaseRatefloat8
True
MonthlyMaintainanceRatefloat8
True
AnnualEscalationint4
True
MonoPagesIncludedint4
True
MonoOveragefloat8
True
ColorPagesIncludedint4
True
ColorOveragefloat8
True
TotalOutputPagesIncludedint4
True
ScannerUseIncludedint4
True
ScannerOveragefloat8
True
FaxUseIncludedint4
True
FaxOveragefloat8
True
MonoCPPvarchar(32)32
True
MonoOverageCPPvarchar(32)32
True
ColorCPPvarchar(32)32
True
ColorOverageCPPvarchar(32)32
True
ScannerCPPvarchar(32)32
True
ScannerOverageCPPvarchar(32)32
True
FaxCPPvarchar(32)32
True
FaxOverageCPPvarchar(32)32
True
UserCreatedbit1
False
((0))
OEMPartNovarchar(36)36
True
Foreign Keys FK_Device_ModelID_VendorModel_ModelID: [dbo].[VendorModel].ModelIDModelIDint4
True
Ownedbit1
True
Leasedbit1
True
CppContractbit1
True
LeaseVendorvarchar(50)50
True
LeaseStartDatedatetime8
True
LeaseMonthsint4
True
LeaseEndDatedatetime8
True
LeaseMonthlyPaymentfloat8
True
LeaseRatefloat8
True
LeasePaymentfloat8
True
LeasePointsfloat8
True
LeaseDepreciationfloat8
True
DepreciationMonthsint4
True
LeaseAnnualEscalationfloat8
True
CovBlackfloat8
True
CovCyanfloat8
True
CovMagentafloat8
True
CovYellowfloat8
True
DateIntroduceddatetime8
True
DateDiscontinueddatetime8
True
SpeedColorint4
True
SpeedMonoint4
True
TimeToFirstPageColorfloat8
True
TimeToFirstPageMonofloat8
True
ResHorizint4
True
ResVertint4
True
StreetPricefloat8
True
MSRPfloat8
True
Coveragefloat8
True
PrintMethodvarchar(120)120
True
GraphicUrlvarchar(240)240
True
OEMProdIDvarchar(36)36
True
LCDDisplayvarchar(36)36
True
FirmwareVernvarchar(256)512
True
PrintQualityvarchar(36)36
True
PrinterStatusvarchar(36)36
True
PreventMaintIntervalfloat8
True
PagesUntilPreventMaintint4
True
NoJobsProcessedint4
True
InstallMeterMonoint4
True
InstallMeterColorint4
True
LastAuditDatedatetime8
True
CalcCPPMonofloat8
True
CalcCPPColorfloat8
True
PrinterErrorStatenvarchar(250)500
True
ServiceContactnvarchar(100)200
True
HostNamevarchar(100)100
True
ColorCoveragefloat8
True
BlackCoverageTypesmallint2
True
CyanCoverageTypesmallint2
True
MagentaCoverageTypesmallint2
True
YellowCoverageTypesmallint2
True
Foreign Keys FK_Device_FirstPrinterAuditID_PrinterAudit_PrinterAuditID: [dbo].[PrinterAudit].FirstPrinterAuditIDFirstPrinterAuditIDint4
True
Foreign Keys FK_Device_LastPrinterAuditID_PrinterAudit_PrinterAuditID: [dbo].[PrinterAudit].LastPrinterAuditIDLastPrinterAuditIDint4
True
OriginalManufacturervarchar(100)100
True
OriginalModelvarchar(100)100
True
OriginalSerialNrvarchar(100)100
True
OriginalMacAddressvarchar(64)64
True
OriginalPrinterIPAddressvarchar(64)64
True
OriginalLocalIDvarchar(100)100
True
OriginalHostNamevarchar(100)100
True
OriginalPrinterNamevarchar(250)250
True
OriginalValuesvarchar(max)max
True
LCDWarningsnvarchar(max)max
True
Foreign Keys FK_Device_LastServiceAuditID_Audit_AuditID: [dbo].[Audit].LastServiceAuditIDLastServiceAuditIDint4
True
Foreign Keys FK_Device_LastSuppliesAuditID_Audit_AuditID: [dbo].[Audit].LastSuppliesAuditIDLastSuppliesAuditIDint4
True
Foreign Keys FK_Device_RefPrinterAuditID_PrinterAudit_PrinterAuditID: [dbo].[PrinterAudit].RefPrinterAuditIDRefPrinterAuditIDint4
True
Foreign Keys FK_Device_RefServiceDataAuditID_Audit_AuditID: [dbo].[Audit].RefServiceDataAuditIDRefServiceDataAuditIDint4
True
BlackCoverageDetailsint4
True
CyanCoverageDetailsint4
True
MagentaCoverageDetailsint4
True
YellowCoverageDetailsint4
True
Foreign Keys FK_Device_LastServiceDetailsAuditID_Audit_AuditID: [dbo].[Audit].LastServiceDetailsAuditIDLastServiceDetailsAuditIDint4
True
FirmwareVersion2nvarchar(256)512
True
FirmwareVersion3nvarchar(256)512
True
FirmwareVersion4nvarchar(256)512
True
IsDuplexbit1
True
PowerActivefloat8
True
PowerIdlefloat8
True
PowerSleep1float8
True
PowerSleep2float8
True
MeterGroup1nvarchar(256)512
True
MeterGroup2nvarchar(256)512
True
InternalFieldsxmlmax
True
LastSupplyErrorStatesint4
True
InvalidAuditsint4
False
((0))
Statetinyint1
False
((0))
BlockedDatedatetime8
True
Foreign Keys FK_Device_BlockedByContactID_Contact_ContactID: [dbo].[Contact].BlockedByContactIDBlockedByContactIDint4
True
PurchaseTermint4
True
PurchasePriceCurrencyIDint4
True
LeasePriceCurrencyIDint4
True
AlertStatustinyint1
False
((0))
TotalAMVint4
True
MonoAMVint4
True
ColorAMVint4
True
NormalizedModelvarchar(100)100
False
MeterSourcetinyint1
False
((0))
SupplySourcetinyint1
False
((0))
LastPageCountTotalint4
True
LastPageCountColorint4
True
KeyNameKey ColumnsUnique
Cluster Primary Key PK_Device_DeviceID: DeviceIDPK_Device_DeviceIDDeviceID
True
NameUpdateDeleteColumns
FK_Device_AccountID_Account_AccountIDCascadeAccountID->[dbo].[Account].[AccountID]
FK_Device_BlockedByContactID_Contact_ContactIDBlockedByContactID->[dbo].[Contact].[ContactID]
FK_Device_FirstPrinterAuditID_PrinterAudit_PrinterAuditIDFirstPrinterAuditID->[dbo].[PrinterAudit].[PrinterAuditID]
FK_Device_LastPrinterAuditID_PrinterAudit_PrinterAuditIDLastPrinterAuditID->[dbo].[PrinterAudit].[PrinterAuditID]
FK_Device_LastServiceAuditID_Audit_AuditIDLastServiceAuditID->[dbo].[Audit].[AuditID]
FK_Device_LastServiceDetailsAuditID_Audit_AuditIDLastServiceDetailsAuditID->[dbo].[Audit].[AuditID]
FK_Device_LastSuppliesAuditID_Audit_AuditIDLastSuppliesAuditID->[dbo].[Audit].[AuditID]
FK_Device_ModelID_VendorModel_ModelIDCascadeSetNullModelID->[dbo].[VendorModel].[ModelID]
FK_Device_RefPrinterAuditID_PrinterAudit_PrinterAuditIDRefPrinterAuditID->[dbo].[PrinterAudit].[PrinterAuditID]
FK_Device_RefServiceDataAuditID_Audit_AuditIDRefServiceDataAuditID->[dbo].[Audit].[AuditID]
CREATE TABLE [dbo].[Device]
(
[DeviceID] [int] NOT NULL IDENTITY(1, 1),
[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_Device_CreateDate] DEFAULT (getdate()),
[EditedFields] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AccountID] [int] NULL,
[Manufacturer] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Model] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SerialNr] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PrinterAssetNr] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ERPEquipID] [varchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ERPData] [varchar] (max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Location] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Description] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MacAddress] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PrinterIPAddress] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LocalID] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Color] [bit] NULL,
[Networked] [bit] NULL,
[DeviceType] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Segment] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MonthlyDutyCycle] [float] NULL,
[Vendor] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SalesRep] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PurchaseDate] [datetime] NULL,
[PurchasePrice] [float] NULL,
[PONumber] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[InstallDate] [datetime] NULL,
[InstallMeter] [int] NULL,
[FirstAuditDate] [datetime] NULL,
[TotalServiceCopies] [int] NULL,
[TotalServiceCalls] [int] NULL,
[LastServiceDate] [datetime] NULL,
[ServiceHistory] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Contact] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OnContract] [bit] NULL,
[ContractID] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ContractVendor] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ContractType] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ContractRate] [float] NULL,
[ContractEndDate] [datetime] NULL,
[MonthlyBaseRate] [float] NULL,
[MonthlyMaintainanceRate] [float] NULL,
[AnnualEscalation] [int] NULL,
[MonoPagesIncluded] [int] NULL,
[MonoOverage] [float] NULL,
[ColorPagesIncluded] [int] NULL,
[ColorOverage] [float] NULL,
[TotalOutputPagesIncluded] [int] NULL,
[ScannerUseIncluded] [int] NULL,
[ScannerOverage] [float] NULL,
[FaxUseIncluded] [int] NULL,
[FaxOverage] [float] NULL,
[MonoCPP] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MonoOverageCPP] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ColorCPP] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ColorOverageCPP] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ScannerCPP] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ScannerOverageCPP] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FaxCPP] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FaxOverageCPP] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UserCreated] [bit] NOT NULL CONSTRAINT [DF_Device_UserCreated] DEFAULT ((0)),
[OEMPartNo] [varchar] (36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ModelID] [int] NULL,
[Owned] [bit] NULL,
[Leased] [bit] NULL,
[CppContract] [bit] NULL,
[LeaseVendor] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LeaseStartDate] [datetime] NULL,
[LeaseMonths] [int] NULL,
[LeaseEndDate] [datetime] NULL,
[LeaseMonthlyPayment] [float] NULL,
[LeaseRate] [float] NULL,
[LeasePayment] [float] NULL,
[LeasePoints] [float] NULL,
[LeaseDepreciation] [float] NULL,
[DepreciationMonths] [int] NULL,
[LeaseAnnualEscalation] [float] NULL,
[CovBlack] [float] NULL,
[CovCyan] [float] NULL,
[CovMagenta] [float] NULL,
[CovYellow] [float] NULL,
[DateIntroduced] [datetime] NULL,
[DateDiscontinued] [datetime] NULL,
[SpeedColor] [int] NULL,
[SpeedMono] [int] NULL,
[TimeToFirstPageColor] [float] NULL,
[TimeToFirstPageMono] [float] NULL,
[ResHoriz] [int] NULL,
[ResVert] [int] NULL,
[StreetPrice] [float] NULL,
[MSRP] [float] NULL,
[Coverage] [float] NULL,
[PrintMethod] [varchar] (120) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GraphicUrl] [varchar] (240) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OEMProdID] [varchar] (36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LCDDisplay] [varchar] (36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FirmwareVer] [nvarchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PrintQuality] [varchar] (36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PrinterStatus] [varchar] (36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PreventMaintInterval] [float] NULL,
[PagesUntilPreventMaint] [int] NULL,
[NoJobsProcessed] [int] NULL,
[InstallMeterMono] [int] NULL,
[InstallMeterColor] [int] NULL,
[LastAuditDate] [datetime] NULL,
[CalcCPPMono] [float] NULL,
[CalcCPPColor] [float] NULL,
[PrinterErrorState] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ServiceContact] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HostName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ColorCoverage] [float] NULL,
[BlackCoverageType] [smallint] NULL,
[CyanCoverageType] [smallint] NULL,
[MagentaCoverageType] [smallint] NULL,
[YellowCoverageType] [smallint] NULL,
[FirstPrinterAuditID] [int] NULL,
[LastPrinterAuditID] [int] NULL,
[OriginalManufacturer] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OriginalModel] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OriginalSerialNr] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OriginalMacAddress] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OriginalPrinterIPAddress] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OriginalLocalID] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OriginalHostName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OriginalPrinterName] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OriginalValues] [varchar] (max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LCDWarnings] [nvarchar] (max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastServiceAuditID] [int] NULL,
[LastSuppliesAuditID] [int] NULL,
[RefPrinterAuditID] [int] NULL,
[RefServiceDataAuditID] [int] NULL,
[BlackCoverageDetails] [int] NULL,
[CyanCoverageDetails] [int] NULL,
[MagentaCoverageDetails] [int] NULL,
[YellowCoverageDetails] [int] NULL,
[LastServiceDetailsAuditID] [int] NULL,
[FirmwareVersion2] [nvarchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FirmwareVersion3] [nvarchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FirmwareVersion4] [nvarchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IsDuplex] [bit] NULL,
[PowerActive] [float] NULL,
[PowerIdle] [float] NULL,
[PowerSleep1] [float] NULL,
[PowerSleep2] [float] NULL,
[MeterGroup1] [nvarchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MeterGroup2] [nvarchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[InternalFields] [xml] NULL,
[LastSupplyErrorStates] [int] NULL,
[InvalidAudits] [int] NOT NULL CONSTRAINT [DF_Device_InvalidAudits] DEFAULT ((0)),
[State] [tinyint] NOT NULL CONSTRAINT [DF_Device_State] DEFAULT ((0)),
[BlockedDate] [datetime] NULL,
[BlockedByContactID] [int] NULL,
[PurchaseTerm] [int] NULL,
[PurchasePriceCurrencyID] [int] NULL,
[LeasePriceCurrencyID] [int] NULL,
[AlertStatus] [tinyint] NOT NULL CONSTRAINT [DF_Device_AlertStatus] DEFAULT ((0)),
[TotalAMV] [int] NULL,
[MonoAMV] [int] NULL,
[ColorAMV] [int] NULL,
[NormalizedModel] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[MeterSource] [tinyint] NOT NULL CONSTRAINT [DF_Device_MeterSource] DEFAULT ((0)),
[SupplySource] [tinyint] NOT NULL CONSTRAINT [DF_Device_SupplySource] DEFAULT ((0)),
[LastPageCountTotal] [int] NULL,
[LastPageCountColor] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Device] ADD CONSTRAINT [PK_Device_DeviceID] PRIMARY KEY CLUSTERED  ([DeviceID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Device] ADD CONSTRAINT [FK_Device_AccountID_Account_AccountID] FOREIGN KEY ([AccountID]) REFERENCES [dbo].[Account] ([AccountID]) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Device] ADD CONSTRAINT [FK_Device_BlockedByContactID_Contact_ContactID] FOREIGN KEY ([BlockedByContactID]) REFERENCES [dbo].[Contact] ([ContactID])
GO
ALTER TABLE [dbo].[Device] ADD CONSTRAINT [FK_Device_FirstPrinterAuditID_PrinterAudit_PrinterAuditID] FOREIGN KEY ([FirstPrinterAuditID]) REFERENCES [dbo].[PrinterAudit] ([PrinterAuditID])
GO
ALTER TABLE [dbo].[Device] ADD CONSTRAINT [FK_Device_LastPrinterAuditID_PrinterAudit_PrinterAuditID] FOREIGN KEY ([LastPrinterAuditID]) REFERENCES [dbo].[PrinterAudit] ([PrinterAuditID])
GO
ALTER TABLE [dbo].[Device] ADD CONSTRAINT [FK_Device_LastServiceAuditID_Audit_AuditID] FOREIGN KEY ([LastServiceAuditID]) REFERENCES [dbo].[Audit] ([AuditID])
GO
ALTER TABLE [dbo].[Device] ADD CONSTRAINT [FK_Device_LastServiceDetailsAuditID_Audit_AuditID] FOREIGN KEY ([LastServiceDetailsAuditID]) REFERENCES [dbo].[Audit] ([AuditID])
GO
ALTER TABLE [dbo].[Device] ADD CONSTRAINT [FK_Device_LastSuppliesAuditID_Audit_AuditID] FOREIGN KEY ([LastSuppliesAuditID]) REFERENCES [dbo].[Audit] ([AuditID])
GO
ALTER TABLE [dbo].[Device] ADD CONSTRAINT [FK_Device_ModelID_VendorModel_ModelID] FOREIGN KEY ([ModelID]) REFERENCES [dbo].[VendorModel] ([ModelID]) ON DELETE SET NULL ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[Device] ADD CONSTRAINT [FK_Device_RefPrinterAuditID_PrinterAudit_PrinterAuditID] FOREIGN KEY ([RefPrinterAuditID]) REFERENCES [dbo].[PrinterAudit] ([PrinterAuditID])
GO
ALTER TABLE [dbo].[Device] ADD CONSTRAINT [FK_Device_RefServiceDataAuditID_Audit_AuditID] FOREIGN KEY ([RefServiceDataAuditID]) REFERENCES [dbo].[Audit] ([AuditID])
GO