Tables [dbo].[LocServ]
Properties
PropertyValue
CollationSQL_Latin1_General_CP1_CI_AS
Row Count (~)25661
Created5:36:48 PM Tuesday, February 27, 2007
Last Modified10:12:23 AM Thursday, June 20, 2013
Columns
NameData TypeMax Length (Bytes)Allow NullsIdentity
Cluster Primary Key PK_LOCSERV: PriKeyIndexes IX_LocServ_ServiceData: ServiceDate\PriKeyStatistics STAT_LocServ_ServiceData: PriKey\ServiceDatePriKeyint4
No
1 - 1
Foreign Keys FK_LOCSERV_ORDERS: [dbo].[Orders].OrdPriKeyIndexes IX_LocServ_OrdPriKey: OrdPriKeyOrdPriKeyint4
No
Indexes IX_LocServ_ServiceData: ServiceDate\PriKeyStatistics STAT_LocServ_ServiceData: PriKey\ServiceDateServiceDatedatetime8
No
WkTicketNovarchar(32)32
Yes
Foreign Keys FK_LOCSERV_ServType: [dbo].[ServType].ServiceIDServiceIDint4
No
JobStartdatetime8
Yes
JobEnddatetime8
Yes
TotCrewHrsdecimal(10,2)9
Yes
HrsPerMandecimal(10,2)9
Yes
Indexes IX_LocServ_Branch_ServStatus: SubmittedOn\BranchPriKey\ServStatusServStatussmallint2
Yes
HrsDrv1Waydecimal(10,2)9
Yes
Quantityint4
Yes
Crewint4
Yes
OriginDestinationvarchar(1)1
Yes
CreatedOndatetime8
No
Foreign Keys FK_LOCSERV_Sysuser: [dbo].[Sysuser].CreatedByCreatedByint4
No
LastEditedOndatetime8
Yes
Foreign Keys FK_LOCSERV_Sysuser1: [dbo].[Sysuser].LastEditedByLastEditedByint4
Yes
Indexes IX_LocServ_Branch_ServStatus: SubmittedOn\BranchPriKey\ServStatusSubmittedOndatetime8
Yes
Foreign Keys FK_LOCSERV_Sysuser2: [dbo].[Sysuser].SubmittedBySubmittedByint4
Yes
ClearedOndatetime8
Yes
Foreign Keys FK_LOCSERV_Sysuser3: [dbo].[Sysuser].ClearedByClearedByint4
Yes
Foreign Keys FK_LOCSERV_BRANCH: [dbo].[Branch].BranchPriKeyIndexes IX_LocServ_Branch_ServStatus: SubmittedOn\BranchPriKey\ServStatusBranchPriKeyint4
No
Foreign Keys FK_LOCSERV_XtraStop1: [dbo].[XtraStop].OriginXtraStopFIDOriginXtraStopFIDint4
Yes
Foreign Keys FK_LOCSERV_XtraStop2: [dbo].[XtraStop].DestXtraStopFIDDestXtraStopFIDint4
Yes
Foreign Keys FK_LOCSERV_DispatchStatusFID: [dbo].[DispatchStatus].DispatchStatusFIDDispatchStatusFIDint4
Yes
Indexes Indexes
NameColumnsUniqueFill Factor
Cluster Primary Key PK_LOCSERV: PriKeyPK_LOCSERVPriKey
Yes
90
IX_LocServ_Branch_ServStatusSubmittedOn, BranchPriKey, ServStatus
IX_LocServ_OrdPriKeyOrdPriKey
IX_LocServ_ServiceDataServiceDate, PriKey
Statistics Statistics
NameColumns
STAT_LocServ_ServiceDataPriKey, ServiceDate
Foreign Keys Foreign Keys
NameNo CheckColumns
FK_LOCSERV_BRANCH
Yes
BranchPriKey->[dbo].[Branch].[BranchPriKey]
FK_LOCSERV_Sysuser3
Yes
ClearedBy->[dbo].[Sysuser].[SysUserID]
FK_LOCSERV_Sysuser
Yes
CreatedBy->[dbo].[Sysuser].[SysUserID]
FK_LOCSERV_XtraStop2
Yes
DestXtraStopFID->[dbo].[XtraStop].[PriKey]
FK_LOCSERV_DispatchStatusFID
Yes
DispatchStatusFID->[dbo].[DispatchStatus].[DispatchStatusID]
FK_LOCSERV_Sysuser1
Yes
LastEditedBy->[dbo].[Sysuser].[SysUserID]
FK_LOCSERV_ORDERS
Yes
OrdPriKey->[dbo].[Orders].[PriKey]
FK_LOCSERV_XtraStop1
Yes
OriginXtraStopFID->[dbo].[XtraStop].[PriKey]
FK_LOCSERV_ServType
Yes
ServiceID->[dbo].[ServType].[ServiceID]
FK_LOCSERV_Sysuser2
Yes
SubmittedBy->[dbo].[Sysuser].[SysUserID]
Permissions
TypeActionOwning Principal
GrantDeleteMssExec
GrantInsertMssExec
GrantSelectMssExec
GrantUpdateMssExec
SQL Script
CREATE TABLE [dbo].[LocServ]
(
[PriKey] [int] NOT NULL IDENTITY(1, 1),
[OrdPriKey] [int] NOT NULL,
[ServiceDate] [datetime] NOT NULL,
[WkTicketNo] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ServiceID] [int] NOT NULL,
[JobStart] [datetime] NULL,
[JobEnd] [datetime] NULL,
[TotCrewHrs] [decimal] (10, 2) NULL,
[HrsPerMan] [decimal] (10, 2) NULL,
[ServStatus] [smallint] NULL,
[HrsDrv1Way] [decimal] (10, 2) NULL,
[Quantity] [int] NULL,
[Crew] [int] NULL,
[OriginDestination] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CreatedOn] [datetime] NOT NULL,
[CreatedBy] [int] NOT NULL,
[LastEditedOn] [datetime] NULL,
[LastEditedBy] [int] NULL,
[SubmittedOn] [datetime] NULL,
[SubmittedBy] [int] NULL,
[ClearedOn] [datetime] NULL,
[ClearedBy] [int] NULL,
[BranchPriKey] [int] NOT NULL,
[OriginXtraStopFID] [int] NULL,
[DestXtraStopFID] [int] NULL,
[DispatchStatusFID] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[LocServ] ADD CONSTRAINT [PK_LOCSERV] PRIMARY KEY CLUSTERED  ([PriKey]) WITH (FILLFACTOR=90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_LocServ_Branch_ServStatus] ON [dbo].[LocServ] ([BranchPriKey], [ServStatus]) INCLUDE ([SubmittedOn]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_LocServ_OrdPriKey] ON [dbo].[LocServ] ([OrdPriKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_LocServ_ServiceData] ON [dbo].[LocServ] ([ServiceDate], [PriKey]) ON [PRIMARY]
GO
CREATE STATISTICS [STAT_LocServ_ServiceData] ON [dbo].[LocServ] ([PriKey], [ServiceDate])
GO
ALTER TABLE [dbo].[LocServ] WITH NOCHECK ADD CONSTRAINT [FK_LOCSERV_BRANCH] FOREIGN KEY ([BranchPriKey]) REFERENCES [dbo].[Branch] ([BranchPriKey])
GO
ALTER TABLE [dbo].[LocServ] WITH NOCHECK ADD CONSTRAINT [FK_LOCSERV_Sysuser3] FOREIGN KEY ([ClearedBy]) REFERENCES [dbo].[Sysuser] ([SysUserID])
GO
ALTER TABLE [dbo].[LocServ] WITH NOCHECK ADD CONSTRAINT [FK_LOCSERV_Sysuser] FOREIGN KEY ([CreatedBy]) REFERENCES [dbo].[Sysuser] ([SysUserID])
GO
ALTER TABLE [dbo].[LocServ] WITH NOCHECK ADD CONSTRAINT [FK_LOCSERV_XtraStop2] FOREIGN KEY ([DestXtraStopFID]) REFERENCES [dbo].[XtraStop] ([PriKey])
GO
ALTER TABLE [dbo].[LocServ] WITH NOCHECK ADD CONSTRAINT [FK_LOCSERV_DispatchStatusFID] FOREIGN KEY ([DispatchStatusFID]) REFERENCES [dbo].[DispatchStatus] ([DispatchStatusID])
GO
ALTER TABLE [dbo].[LocServ] WITH NOCHECK ADD CONSTRAINT [FK_LOCSERV_Sysuser1] FOREIGN KEY ([LastEditedBy]) REFERENCES [dbo].[Sysuser] ([SysUserID])
GO
ALTER TABLE [dbo].[LocServ] WITH NOCHECK ADD CONSTRAINT [FK_LOCSERV_ORDERS] FOREIGN KEY ([OrdPriKey]) REFERENCES [dbo].[Orders] ([PriKey])
GO
ALTER TABLE [dbo].[LocServ] WITH NOCHECK ADD CONSTRAINT [FK_LOCSERV_XtraStop1] FOREIGN KEY ([OriginXtraStopFID]) REFERENCES [dbo].[XtraStop] ([PriKey])
GO
ALTER TABLE [dbo].[LocServ] WITH NOCHECK ADD CONSTRAINT [FK_LOCSERV_ServType] FOREIGN KEY ([ServiceID]) REFERENCES [dbo].[ServType] ([ServiceID])
GO
ALTER TABLE [dbo].[LocServ] WITH NOCHECK ADD CONSTRAINT [FK_LOCSERV_Sysuser2] FOREIGN KEY ([SubmittedBy]) REFERENCES [dbo].[Sysuser] ([SysUserID])
GO
GRANT SELECT ON  [dbo].[LocServ] TO [MssExec]
GRANT INSERT ON  [dbo].[LocServ] TO [MssExec]
GRANT DELETE ON  [dbo].[LocServ] TO [MssExec]
GRANT UPDATE ON  [dbo].[LocServ] TO [MssExec]
GO
Uses
Used By