Summary Table

Categories Total Count
PII 0
URL 0
DNS 0
EKL 0
IP 0
PORT 0
VsID 0
CF 0
AI 0
VPD 0
PL 0
Other 0

File Content

?CREATE TABLE zraw._conConsult
(
--fields

ConsultSID bigint NOT NULL,
ConsultIEN varchar (50) NOT NULL,
Sta3n smallint NOT NULL,
PatientSID int NULL,
PatientLocationSID int NULL,
ProtocolSID int NULL,
GMRCProcedureSID int NULL,
GMRCProcedure varchar (100) NULL,
ClinicalProcedureSID int NULL,
ToRequestServiceSID int NULL,
ToRequestServiceName varchar (100) NULL,
RequestDateTime datetime2 NULL,
RequestVistaErrorDate varchar (50) NULL,
RequestDateTimeTransformSID bigint NULL,
RequestDateSID int NULL,
FileEntryDateTime datetime2 NULL,
FileEntryVistaErrorDate varchar (50) NULL,
FileEntryDateTimeTransformSID bigint NULL,
FromLocationSID int NULL,
RequestType varchar (50) NULL,
ToStaffSID int NULL,
OrderStatusSID int NULL,
CPRSOrderSID bigint NULL,
CPRSStatus varchar (50) NULL,
RecentActivityTypeSID int NULL,
RecentActivityType varchar (100) NULL,
SendingStaffSID int NULL,
SignificantFindings char (1) NULL,
UrgencyProtocolSID int NULL,
Urgency varchar (50) NULL,
ConsultPlaceProtocolSID int NULL,
PlaceOfConsultation varchar (50) NULL,
DisplayTextOfItemOrdered varchar (100) NULL,
OrderingInstitutionSID int NULL,
OrderingSta3n smallint NULL,
InpatOutpat varchar (50) NULL,
ProvisionalDiagnosis varchar (255) NULL,
ProvisionalDiagnosisCode varchar (50) NULL,
RemoteRole varchar (50) NULL,
RemoteService varchar (100) NULL,
RemoteConsultInstitutionSID int NULL,
RemoteConsultSta3n varchar (50) NULL,
RemoteConsultSID bigint NULL,
TIUDocumentSID bigint NULL,
EarliestDate date NULL,
EarliestVistaErrorDate varchar (50) NULL,
EarliestDateTransformSID bigint NULL,
ClinicalProcedureIEN varchar (50) NULL,
ConsultPlaceProtocolIEN varchar (50) NULL,
CPRSOrderIEN varchar (50) NULL,
FromLocationIEN varchar (50) NULL,
GMRCProcedureIEN varchar (50) NULL,
OrderingInstitutionIEN varchar (50) NULL,
OrderStatusIEN varchar (50) NULL,
PatientIEN varchar (50) NULL,
PatientLocationIEN varchar (50) NULL,
ProtocolIEN varchar (50) NULL,
RecentActivityTypeIEN varchar (50) NULL,
RemoteConsultIEN varchar (50) NULL,
RemoteConsultInstitutionIEN varchar (50) NULL,
SendingStaffIEN varchar (50) NULL,
TIUDocumentIEN varchar (50) NULL,
ToRequestServiceIEN varchar (50) NULL,
ToStaffIEN varchar (50) NULL,
UrgencyProtocolIEN varchar (50) NULL,
ResultIEN varchar (50) NULL,
ResultSID bigint NULL,
ResultPointedToFileNumber varchar (50) NULL,
ETLBatchID int,
OpCode char (1) NOT NULL,
VistaCreateDate datetime NULL,
VistaEditDate datetime NULL,
ExtractBatchID bigint NULL,
--BatchLogID should always be the last of the authoritative source fields listed
--in the data gathered by extractors, it should be the first of the EPRS-specific fields
BatchLogID BIGINT,
--EPRS table specific: Any fields beyond this point are EPRS specific fields/FKs
StatusTypeID tinyint NULL,
--RequestServiceID int NULL,
StationID smallint NULL,

OneConsultID bigint NULL, --FK to OneConsults
PatientID bigint NULL, --EPRS assigned (FK), referencess Patients
RequestServiceID bigint NULL,
OrderingStationID smallint NULL, --EPRS assigned (FK), references Stations
RemoteConsultStationID smallint NULL, --EPRS assigned (FK), references Stations
SendingStaffUserID bigint NULL, --EPRS assigned (FK), referencess StaffUsers
ToStaffUserID bigint NULL, --EPRS assigned (FK), referencess StaffUsers
InstitutionID tinyint NULL, --EPRS assigned (FK), references Institutions
OrderingInstitutionID tinyint NULL, --EPRS assigned (FK), references Institutions
RemoteConsultInstitutionID tinyint NULL, --EPRS assigned (FK), references Institutions
PatientLocationID int NULL, --EPRS assigned (FK), references Locations
FromLocationID int NULL, --EPRS assigned (FK), references Locations
UrgencyProtocolID int NULL, --EPRS assigned (FK), referencess Protocals
ConsultPlaceProtocolID int NULL, --EPRS assigned (FK), referencess Protocals
ToRequestServiceSectionID int NULL, --EPRS assigned (FK), references ServiceSections
GMRCProcedureSubspecialtyTypeID int NULL, --EPRS assigned (FK), references ProcedureSubspeicaltyTypes
ClinicalProcedureSubspecialtyTypeID int NULL, --EPRS assigned (FK), references ProcedureSubspeicaltyTypes
OrderStatusID tinyint NULL, --EPRS assigned (FK), references Statuses
CPRSStatusID tinyint NULL, --EPRS assigned (FK), references Statuses
ActivityTypeID int NULL, --EPRS assigned (FK), references ActivityTypes
RecentActivityTypeID int NULL, --EPRS assigned (FK), references ActivityTypes
RecordStatusID tinyint NULL, --EPRS assigned (Fk), referencess RecordStatuses
TIUDocumentID int NULL, --EPRS Assigned (FK), referencess TIUDocuments

--primary key

--unique key (if any)
)

--when using clustered columnstore index, can't set data compression to PAGE
WITH (DATA_COMPRESSION = PAGE)

GO

--foreign keys

--indexes
CREATE NONCLUSTERED INDEX IX_conConsult_ConsultSID ON zraw._conConsult
(
ConsultSID
) WITH (
PAD_INDEX = ON, --If want to use a Fill Factor, then PAD_INDEX must = ON
FILLFACTOR = 100, --100 = max fill; set to 90 if going to insert new values incrementally; 100 if doing bulk load
SORT_IN_TEMPDB = ON, -- sorts the index in the TempDB; default = OFF
IGNORE_DUP_KEY = OFF,
STATISTICS_NORECOMPUTE = OFF,
STATISTICS_INCREMENTAL = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
MAXDOP = 0, --degrees of parallelism, controls how many CPUs to use; 0 = default (all available), max = 64
DATA_COMPRESSION = PAGE --use page compression; default = NONE
)
ON CoreIndex
GO

CREATE NONCLUSTERED INDEX IX_conConsult_BatchLogID ON zraw._conConsult
(
BatchLogID
) WITH (
PAD_INDEX = ON, --If want to use a Fill Factor, then PAD_INDEX must = ON
FILLFACTOR = 100, --100 = max fill; set to 90 if going to insert new values incrementally; 100 if doing bulk load
SORT_IN_TEMPDB = ON, -- sorts the index in the TempDB; default = OFF
IGNORE_DUP_KEY = OFF,
STATISTICS_NORECOMPUTE = OFF,
STATISTICS_INCREMENTAL = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
MAXDOP = 0, --degrees of parallelism, controls how many CPUs to use; 0 = default (all available), max = 64
DATA_COMPRESSION = PAGE --use page compression; default = NONE
)
ON CoreIndex
GO

--constraints

--Metadata descriptions for each field