OpenSAFELY-TPP Database Schema

This report displays the schema of the OpenSAFELY-TPP database. It is part of the OpenSAFELY platform's technical documentation and is published at https://reports.opensafely.org/.

The schema is read from the OpenSAFELYSchemaInformation table, which is refreshed at the same time as the core SystmOne primary care datasets. There are some non-automated steps required to update the schema information when a new table is added to the database — if you can't see a table that you are expecting to see, then please speak to TPP.

If you would like to use the OpenSAFELY platform, then you should read our documentation, our principles, and our process for onboarding new users. If you would like to see the code we used to create this report, then you can view it on GitHub.

Data Sources

The core SystmOne primary care datasets are stored in the S1 tables in the database. The table below lists other, externally-linked datasets:

Dataset Source Tables
All positive or negative SARS-CoV2 tests SGSS SGSS_AllTests_Positive, SGSS_AllTests_Negative
First-ever positive or negative SARS-CoV2 test SGSS SGSS_Positive, SGSS_Negative
A&E attendances SUS Emergency Care EC
In-patient hospital admissions SUS Admitted Patient Care Spells APCS
Out-patient hospital appointments SUS OPA
Covid-related ICU admissions ICNARC ICNARC
Covid-related in-hospital deaths CPNS CPNS
COVID-19 Infection Survey ONS ONS_CIS
All-cause registered deaths ONS ONS_Deaths
High cost drugs HighCostDrugs
Unique Property Reference Number1 UPRN
Master Patient Index MPI
Health and Social Care Worker identification2 HealthCareWorker

1Used for deriving household variables
2Collected at the point of vaccination

Some of these tables are accompanied by additional tables. For example, the OPA table contains the core out-patient appointment event data, and is accompanied by the OPA_Cost, OPA_Diag, and OPA_Proc tables.

Report Run Date

This report was run on 2022-10-12. It reflects the state of the database on this date.

Table Names by Data Source

DataSource TableName
ICNARC ICNARC
NHSD_HIGH_COST_DRUGS HighCostDrugs
NHSE_APCS APCS
NHSE_APCS APCS_Cost
NHSE_APCS APCS_Der
NHSE_CPNS CPNS
NHSE_EC EC
NHSE_EC EC_AlcoholDrugInvolvement
NHSE_EC EC_Comorbidities
NHSE_EC EC_Cost
NHSE_EC EC_Diagnosis
NHSE_EC EC_Investigation
NHSE_EC EC_PatientMentalHealth
NHSE_EC EC_Treatment
NHSE_ECDS ECDS
NHSE_ECDS ECDS_EC_Diagnoses
NHSE_HEALTH_CARE_WORKER HealthCareWorker
NHSE_MPI MPI
NHSE_MPI UPRN
NHSE_OPA OPA
NHSE_OPA OPA_Cost
NHSE_OPA OPA_Diag
NHSE_OPA OPA_Proc
NHSE_SGSS SGSS_AllTests_Negative
NHSE_SGSS SGSS_AllTests_Positive
NHSE_SGSS SGSS_Negative
NHSE_SGSS SGSS_Positive
NHSE_Therapeutics Therapeutics
NHSE_WL WL_ClockStops
NHSE_WL WL_Diagnostics
NHSE_WL WL_OpenPathways
ONS_CIS ONS_CIS
ONS_DEATHS ONS_Deaths
OS_BUILD BuildInfo
OS_BUILD CodeCountIndicator
OS_BUILD LatestBuildTime
OS_BUILD OpenSAFELYSchemaInformation
OS_DERIVED Household
OS_DERIVED HouseholdMember
OS_DERIVED MSOA_PopulationEstimates_2019
OS_DERIVED PotentialCareHomeAddress
S1 Appointment
S1 CodedEvent
S1 CodedEventRange
S1 Consultation
S1 CTV3Dictionary
S1 CTV3Hierarchy
S1 DataDictionary
S1 ICD10Dictionary
S1 MedicationDictionary
S1 MedicationIssue
S1 MedicationRepeat
S1 MedicationSensitivity
S1 Organisation
S1 Patient
S1 PatientAddress
S1 QOFClusterReference
S1 RegistrationHistory
S1 UnitDictionary
S1 Vaccination
S1 VaccinationReference
S1 YCodeToSnomedMapping
UK_Renal_Register UKRR

Table Schemas

The table schemas contain the following information:

Use the links in the list below to jump to a table schema.

ICNARC

ICNARC

ColumnName ColumnType Precision Scale MaxLength IsNullable
AdvancedDays_CardiovascularSupport int 10 0 4 True
AdvancedDays_RespiratorySupport int 10 0 4 True
ahsurv int 10 0 4 True
AP2score int 10 0 4 True
ausurv int 10 0 4 True
BasicDays_CardiovascularSupport int 10 0 4 True
BasicDays_RespiratorySupport int 10 0 4 True
CalculatedAge int 10 0 4 True
DateOfDeath datetime 23 3 8 True
EstimatedAge int 10 0 4 True
HighestLevelFirst24Hours int 10 0 4 True
HospitalAdmissionDate datetime 23 3 8 True
HospitalDischargeDate datetime 23 3 8 True
HRG varchar 0 0 50 True
ICNARC_ID bigint 19 0 8 True
IcuAdmissionDateTime datetime 23 3 8 True
IcuDischargeDateTime datetime 23 3 8 True
IMscore int 10 0 4 True
Level0days int 10 0 4 True
Level1days int 10 0 4 True
Level2days int 10 0 4 True
Level3days int 10 0 4 True
OriginalHospitalAdmissionDate datetime 23 3 8 True
OriginalIcuAdmissionDate datetime 23 3 8 True
Patient_ID bigint 19 0 8 False
pfratio real 24 0 4 True
Sex varchar 0 0 10 True
SupportDays_Dermatological int 10 0 4 True
SupportDays_Gastrointestinal int 10 0 4 True
SupportDays_Liver int 10 0 4 True
SupportDays_Neurological int 10 0 4 True
SupportDays_Renal int 10 0 4 True
TransferredIn varchar 0 0 10 True
TransferredOut varchar 0 0 10 True
UltimateHospitalDischargeDate datetime 23 3 8 True
UltimateIcuDischargeDate datetime 23 3 8 True
Ventilator int 10 0 4 True
yhsurv int 10 0 4 True
yusurv int 10 0 4 True

NHSD_HIGH_COST_DRUGS

HighCostDrugs

ColumnName ColumnType Precision Scale MaxLength IsNullable
ActivityTreatmentFunctionCode varchar 0 0 100 True
DerivedSNOMEDFromName varchar 0 0 1000 True
DerivedVTM varchar 0 0 1000 True
DerivedVTMName varchar 0 0 1000 True
DispensingRoute varchar 0 0 100 True
DrugName varchar 0 0 1000 True
DrugPackSize varchar 0 0 1000 True
DrugQuanitityOrWeightProportion varchar 0 0 1000 True
DrugStrength varchar 0 0 1000 True
DrugVolume varchar 0 0 1000 True
FinancialMonth varchar 0 0 2 True
FinancialYear varchar 0 0 6 True
HighCostTariffExcludedDrugCode varchar 0 0 100 True
HomeDeliveryCharge varchar 0 0 100 True
Patient_ID bigint 19 0 8 False
PersonAge int 10 0 4 True
PersonGender int 10 0 4 True
RouteOfAdministration varchar 0 0 100 True
TherapeuticIndicationCode varchar 0 0 1000 True
TotalCost varchar 0 0 100 True
UnitOfMeasurement varchar 0 0 100 True

NHSE_APCS

APCS

ColumnName ColumnType Precision Scale MaxLength IsNullable
Administrative_Category varchar 0 0 2 True
Admission_Date date 10 0 3 True
Admission_Method varchar 0 0 2 True
APCS_Ident bigint 19 0 8 False
Carer_Support_Indicator varchar 0 0 1000 True
Der_Activity_Month varchar 0 0 6 True
Der_Admit_Treatment_Function_Code varchar 0 0 3 True
Der_Diagnosis_All varchar 0 0 4000 True
Der_Diagnosis_Count int 10 0 4 True
Der_Dischg_Treatment_Function_Code varchar 0 0 3 True
Der_Financial_Year varchar 0 0 7 True
Der_Procedure_All varchar 0 0 4000 True
Der_Procedure_Count int 10 0 4 True
Der_Pseudo_Patient_Pathway_ID bigint 19 0 8 True
Der_Spell_LoS int 10 0 4 True
Discharge_Date date 10 0 3 True
Discharge_Destination varchar 0 0 2 True
Discharge_Method varchar 0 0 2 True
Duration_of_Elective_Wait int 10 0 4 True
Ethnic_Group varchar 0 0 2 True
Hospital_Spell_Duration int 10 0 4 True
Patient_Classification varchar 0 0 2 True
Patient_ID bigint 19 0 8 False
Provider_Org_Code_Type varchar 0 0 5 True
Source_of_Admission varchar 0 0 2 True
Spell_Core_HRG_SUS varchar 0 0 10 True
Spell_HRG_Version_No_SUS varchar 0 0 20 True

APCS_Cost

ColumnName ColumnType Precision Scale MaxLength IsNullable
APCS_Ident bigint 19 0 8 False
Grand_Total_Payment_MFF real 24 0 4 True
Patient_ID bigint 19 0 8 False
Tariff_Initial_Amount real 24 0 4 True
Tariff_Total_Payment real 24 0 4 True

APCS_Der

ColumnName ColumnType Precision Scale MaxLength IsNullable
APCS_Ident bigint 19 0 8 False
Patient_ID bigint 19 0 8 False
Spell_Dominant_Procedure varchar 0 0 100 True
Spell_LoS varchar 0 0 5 True
Spell_Main_Specialty_Code varchar 0 0 3 True
Spell_PbR_CC_Day varchar 0 0 4 True
Spell_PbR_Rehab_Days varchar 0 0 4 True
Spell_Primary_Diagnosis varchar 0 0 5 True
Spell_RE30_Admit_Type varchar 0 0 1 True
Spell_RE30_Indicator varchar 0 0 1 True
Spell_Secondary_Diagnosis varchar 0 0 5 True
Spell_Treatment_Function_Code varchar 0 0 3 True

NHSE_CPNS

CPNS

ColumnName ColumnType Precision Scale MaxLength IsNullable
Age int 10 0 4 True
CovidTestResult varchar 0 0 100 True
DateOfAdmission date 10 0 3 True
DateOfDeath date 10 0 3 True
DateOfResult date 10 0 3 True
DateOfSwabbed date 10 0 3 True
Der_Ethnic_Category_Description varchar 0 0 100 True
Der_Latest_SUS_Attendance_Date_For_Ethnicity varchar 0 0 100 True
Der_Source_Dataset_For_Ethnicty varchar 0 0 100 True
DetainedUnderMHAct bit 1 0 1 True
HadLearningDisability varchar 0 0 10 True
Id bigint 19 0 8 False
LearningDisabilityType varchar 0 0 100 True
LocationOfDeath varchar 0 0 1000 True
NationalApproved varchar 0 0 10 True
NationalApprovedDate date 10 0 3 True
NHSworker bit 1 0 1 True
OnDeathCertificateNotice bit 1 0 1 True
OrganisationTypeLot varchar 0 0 100 True
Patient_ID bigint 19 0 8 False
PreExistingCondition varchar 0 0 10 True
PreExistingConditionList varchar 0 0 4000 True
ReceivedTreatmentForMentalHealth varchar 0 0 100 True
RegionalApprovedDate date 10 0 3 True
RegionApproved varchar 0 0 10 True
RegionCode varchar 0 0 10 True
RegionName varchar 0 0 100 True
RelativesAware varchar 0 0 100 True
Sex varchar 0 0 5 True
snapDate date 10 0 3 True
TransferredFromAMentalHealthSetting bit 1 0 1 True
TransferredFromLearningDisabilityAutismSetting bit 1 0 1 True
TravelHistory varchar 0 0 10 True

NHSE_EC

EC

ColumnName ColumnType Precision Scale MaxLength IsNullable
Arrival_Date date 10 0 3 True
Arrival_Time time 16 7 5 True
Decision_To_Admit_Treatment_Function_Code varchar 0 0 3 True
Der_Activity_Month varchar 0 0 6 True
Der_EC_Diagnosis_All varchar 0 0 4000 True
Der_EC_Investigation_All varchar 0 0 4000 True
Der_EC_Treatment_All varchar 0 0 4000 True
Der_Financial_Year varchar 0 0 7 True
Discharge_Destination_SNOMED_CT varchar 0 0 20 True
DQ_Chief_Complaint_Completed varchar 0 0 5 True
DQ_Chief_Complaint_Expected varchar 0 0 5 True
DQ_Chief_Complaint_Valid varchar 0 0 5 True
DQ_Primary_Diagnosis_Completed varchar 0 0 5 True
DQ_Primary_Diagnosis_Expected varchar 0 0 5 True
DQ_Primary_Diagnosis_Valid varchar 0 0 5 True
EC_Arrival_Mode_SNOMED_CT varchar 0 0 20 True
EC_Attendance_Source_SNOMED_CT varchar 0 0 20 True
EC_AttendanceCategory varchar 0 0 1 True
EC_Chief_Complaint_SNOMED_CT varchar 0 0 20 True
EC_Decision_To_Admit_Date date 10 0 3 True
EC_Department_Type varchar 0 0 2 True
EC_Ident bigint 19 0 8 False
EC_Injury_Date date 10 0 3 True
Ethnic_Category varchar 0 0 1 True
Patient_ID bigint 19 0 8 False
SUS_Final_Price varchar 0 0 5 True
SUS_HRG_Code varchar 0 0 5 True
SUS_Tariff varchar 0 0 5 True

EC_AlcoholDrugInvolvement

ColumnName ColumnType Precision Scale MaxLength IsNullable
EC_Alcohol_Drug_Involvement_01 varchar 0 0 20 True
EC_Alcohol_Drug_Involvement_02 varchar 0 0 20 True
EC_Alcohol_Drug_Involvement_03 varchar 0 0 20 True
EC_Alcohol_Drug_Involvement_04 varchar 0 0 20 True
EC_Alcohol_Drug_Involvement_05 varchar 0 0 20 True
EC_Alcohol_Drug_Involvement_06 varchar 0 0 20 True
EC_Alcohol_Drug_Involvement_07 varchar 0 0 20 True
EC_Alcohol_Drug_Involvement_08 varchar 0 0 20 True
EC_Alcohol_Drug_Involvement_09 varchar 0 0 20 True
EC_Alcohol_Drug_Involvement_10 varchar 0 0 20 True
EC_Alcohol_Drug_Involvement_11 varchar 0 0 20 True
EC_Alcohol_Drug_Involvement_12 varchar 0 0 20 True
EC_Ident bigint 19 0 8 False
Is_Code_Approved_01 varchar 0 0 5 True
Is_Code_Approved_02 varchar 0 0 5 True
Is_Code_Approved_03 varchar 0 0 5 True
Is_Code_Approved_04 varchar 0 0 5 True
Is_Code_Approved_05 varchar 0 0 5 True
Is_Code_Approved_06 varchar 0 0 5 True
Is_Code_Approved_07 varchar 0 0 5 True
Is_Code_Approved_08 varchar 0 0 5 True
Is_Code_Approved_09 varchar 0 0 5 True
Is_Code_Approved_10 varchar 0 0 5 True
Is_Code_Approved_11 varchar 0 0 5 True
Is_Code_Approved_12 varchar 0 0 5 True
Patient_ID bigint 19 0 8 False

EC_Comorbidities

ColumnName ColumnType Precision Scale MaxLength IsNullable
Comorbidity_01 varchar 0 0 20 True
Comorbidity_02 varchar 0 0 20 True
Comorbidity_03 varchar 0 0 20 True
Comorbidity_04 varchar 0 0 20 True
Comorbidity_05 varchar 0 0 20 True
Comorbidity_06 varchar 0 0 20 True
Comorbidity_07 varchar 0 0 20 True
Comorbidity_08 varchar 0 0 20 True
Comorbidity_09 varchar 0 0 20 True
Comorbidity_10 varchar 0 0 20 True
Comorbidity_11 varchar 0 0 20 True
Comorbidity_12 varchar 0 0 20 True
Comorbidity_13 varchar 0 0 20 True
Comorbidity_14 varchar 0 0 20 True
Comorbidity_15 varchar 0 0 20 True
Comorbidity_16 varchar 0 0 20 True
Comorbidity_17 varchar 0 0 20 True
Comorbidity_18 varchar 0 0 20 True
Comorbidity_19 varchar 0 0 20 True
Comorbidity_20 varchar 0 0 20 True
Comorbidity_21 varchar 0 0 20 True
Comorbidity_22 varchar 0 0 20 True
Comorbidity_23 varchar 0 0 20 True
Comorbidity_24 varchar 0 0 20 True
EC_Ident bigint 19 0 8 False
Patient_ID bigint 19 0 8 False

EC_Cost

ColumnName ColumnType Precision Scale MaxLength IsNullable
EC_Ident bigint 19 0 8 False
Grand_Total_Payment_MFF real 24 0 4 True
Patient_ID bigint 19 0 8 False
Tariff_Total_Payment real 24 0 4 True

EC_Diagnosis

ColumnName ColumnType Precision Scale MaxLength IsNullable
AEA_Diagnosis_01 varchar 0 0 100 True
AEA_Diagnosis_02 varchar 0 0 100 True
AEA_Diagnosis_03 varchar 0 0 100 True
AEA_Diagnosis_04 varchar 0 0 100 True
AEA_Diagnosis_05 varchar 0 0 100 True
AEA_Diagnosis_06 varchar 0 0 100 True
AEA_Diagnosis_07 varchar 0 0 100 True
AEA_Diagnosis_08 varchar 0 0 100 True
AEA_Diagnosis_09 varchar 0 0 100 True
AEA_Diagnosis_10 varchar 0 0 100 True
AEA_Diagnosis_11 varchar 0 0 100 True
AEA_Diagnosis_12 varchar 0 0 100 True
AEA_Diagnosis_13 varchar 0 0 100 True
AEA_Diagnosis_14 varchar 0 0 100 True
AEA_Diagnosis_15 varchar 0 0 100 True
AEA_Diagnosis_16 varchar 0 0 100 True
AEA_Diagnosis_17 varchar 0 0 100 True
AEA_Diagnosis_18 varchar 0 0 100 True
AEA_Diagnosis_19 varchar 0 0 100 True
AEA_Diagnosis_20 varchar 0 0 100 True
AEA_Diagnosis_21 varchar 0 0 100 True
AEA_Diagnosis_22 varchar 0 0 100 True
AEA_Diagnosis_23 varchar 0 0 100 True
AEA_Diagnosis_24 varchar 0 0 100 True
EC_Chief_Complaint_SNOMED_CT varchar 0 0 20 True
EC_Diagnosis_01 varchar 0 0 20 True
EC_Diagnosis_02 varchar 0 0 20 True
EC_Diagnosis_03 varchar 0 0 20 True
EC_Diagnosis_04 varchar 0 0 20 True
EC_Diagnosis_05 varchar 0 0 20 True
EC_Diagnosis_06 varchar 0 0 20 True
EC_Diagnosis_07 varchar 0 0 20 True
EC_Diagnosis_08 varchar 0 0 20 True
EC_Diagnosis_09 varchar 0 0 20 True
EC_Diagnosis_10 varchar 0 0 20 True
EC_Diagnosis_11 varchar 0 0 20 True
EC_Diagnosis_12 varchar 0 0 20 True
EC_Diagnosis_13 varchar 0 0 20 True
EC_Diagnosis_14 varchar 0 0 20 True
EC_Diagnosis_15 varchar 0 0 20 True
EC_Diagnosis_16 varchar 0 0 20 True
EC_Diagnosis_17 varchar 0 0 20 True
EC_Diagnosis_18 varchar 0 0 20 True
EC_Diagnosis_19 varchar 0 0 20 True
EC_Diagnosis_20 varchar 0 0 20 True
EC_Diagnosis_21 varchar 0 0 20 True
EC_Diagnosis_22 varchar 0 0 20 True
EC_Diagnosis_23 varchar 0 0 20 True
EC_Diagnosis_24 varchar 0 0 20 True
EC_Ident bigint 19 0 8 False
Patient_ID bigint 19 0 8 False

EC_Investigation

ColumnName ColumnType Precision Scale MaxLength IsNullable
AEA_Investigation_01 varchar 0 0 20 True
AEA_Investigation_02 varchar 0 0 20 True
AEA_Investigation_03 varchar 0 0 20 True
AEA_Investigation_04 varchar 0 0 20 True
AEA_Investigation_05 varchar 0 0 20 True
AEA_Investigation_06 varchar 0 0 20 True
AEA_Investigation_07 varchar 0 0 20 True
AEA_Investigation_08 varchar 0 0 20 True
AEA_Investigation_09 varchar 0 0 20 True
AEA_Investigation_10 varchar 0 0 20 True
AEA_Investigation_11 varchar 0 0 20 True
AEA_Investigation_12 varchar 0 0 20 True
AEA_Investigation_13 varchar 0 0 20 True
AEA_Investigation_14 varchar 0 0 20 True
AEA_Investigation_15 varchar 0 0 20 True
AEA_Investigation_16 varchar 0 0 20 True
AEA_Investigation_17 varchar 0 0 20 True
AEA_Investigation_18 varchar 0 0 20 True
AEA_Investigation_19 varchar 0 0 20 True
AEA_Investigation_20 varchar 0 0 20 True
AEA_Investigation_21 varchar 0 0 20 True
AEA_Investigation_22 varchar 0 0 20 True
AEA_Investigation_23 varchar 0 0 20 True
AEA_Investigation_24 varchar 0 0 20 True
EC_Ident bigint 19 0 8 False
EC_Investigation_01 varchar 0 0 20 True
EC_Investigation_02 varchar 0 0 20 True
EC_Investigation_03 varchar 0 0 20 True
EC_Investigation_04 varchar 0 0 20 True
EC_Investigation_05 varchar 0 0 20 True
EC_Investigation_06 varchar 0 0 20 True
EC_Investigation_07 varchar 0 0 20 True
EC_Investigation_08 varchar 0 0 20 True
EC_Investigation_09 varchar 0 0 20 True
EC_Investigation_10 varchar 0 0 20 True
EC_Investigation_11 varchar 0 0 20 True
EC_Investigation_12 varchar 0 0 20 True
EC_Investigation_13 varchar 0 0 20 True
EC_Investigation_14 varchar 0 0 20 True
EC_Investigation_15 varchar 0 0 20 True
EC_Investigation_16 varchar 0 0 20 True
EC_Investigation_17 varchar 0 0 20 True
EC_Investigation_18 varchar 0 0 20 True
EC_Investigation_19 varchar 0 0 20 True
EC_Investigation_20 varchar 0 0 20 True
EC_Investigation_21 varchar 0 0 20 True
EC_Investigation_22 varchar 0 0 20 True
EC_Investigation_23 varchar 0 0 20 True
EC_Investigation_24 varchar 0 0 20 True
Patient_ID bigint 19 0 8 False

EC_PatientMentalHealth

ColumnName ColumnType Precision Scale MaxLength IsNullable
EC_Ident bigint 19 0 8 False
MH_Classification_01 varchar 0 0 20 True
MH_Classification_010 varchar 0 0 20 True
MH_Classification_011 varchar 0 0 20 True
MH_Classification_012 varchar 0 0 20 True
MH_Classification_013 varchar 0 0 20 True
MH_Classification_014 varchar 0 0 20 True
MH_Classification_015 varchar 0 0 20 True
MH_Classification_016 varchar 0 0 20 True
MH_Classification_017 varchar 0 0 20 True
MH_Classification_018 varchar 0 0 20 True
MH_Classification_019 varchar 0 0 20 True
MH_Classification_02 varchar 0 0 20 True
MH_Classification_03 varchar 0 0 20 True
MH_Classification_04 varchar 0 0 20 True
MH_Classification_05 varchar 0 0 20 True
MH_Classification_06 varchar 0 0 20 True
MH_Classification_07 varchar 0 0 20 True
MH_Classification_08 varchar 0 0 20 True
MH_Classification_09 varchar 0 0 20 True
MH_Classification_20 varchar 0 0 20 True
MH_Classification_21 varchar 0 0 20 True
MH_Classification_22 varchar 0 0 20 True
MH_Classification_23 varchar 0 0 20 True
MH_Classification_24 varchar 0 0 20 True
MH_Expiry_Date_01 date 10 0 3 True
MH_Expiry_Date_010 date 10 0 3 True
MH_Expiry_Date_011 date 10 0 3 True
MH_Expiry_Date_012 date 10 0 3 True
MH_Expiry_Date_013 date 10 0 3 True
MH_Expiry_Date_014 date 10 0 3 True
MH_Expiry_Date_015 date 10 0 3 True
MH_Expiry_Date_016 date 10 0 3 True
MH_Expiry_Date_017 date 10 0 3 True
MH_Expiry_Date_018 date 10 0 3 True
MH_Expiry_Date_019 date 10 0 3 True
MH_Expiry_Date_02 date 10 0 3 True
MH_Expiry_Date_03 date 10 0 3 True
MH_Expiry_Date_04 date 10 0 3 True
MH_Expiry_Date_05 date 10 0 3 True
MH_Expiry_Date_06 date 10 0 3 True
MH_Expiry_Date_07 date 10 0 3 True
MH_Expiry_Date_08 date 10 0 3 True
MH_Expiry_Date_09 date 10 0 3 True
MH_Expiry_Date_20 date 10 0 3 True
MH_Expiry_Date_21 date 10 0 3 True
MH_Expiry_Date_22 date 10 0 3 True
MH_Expiry_Date_23 date 10 0 3 True
MH_Expiry_Date_24 date 10 0 3 True
MH_Start_Date_01 date 10 0 3 True
MH_Start_Date_010 date 10 0 3 True
MH_Start_Date_011 date 10 0 3 True
MH_Start_Date_012 date 10 0 3 True
MH_Start_Date_013 date 10 0 3 True
MH_Start_Date_014 date 10 0 3 True
MH_Start_Date_015 date 10 0 3 True
MH_Start_Date_016 date 10 0 3 True
MH_Start_Date_017 date 10 0 3 True
MH_Start_Date_018 date 10 0 3 True
MH_Start_Date_019 date 10 0 3 True
MH_Start_Date_02 date 10 0 3 True
MH_Start_Date_03 date 10 0 3 True
MH_Start_Date_04 date 10 0 3 True
MH_Start_Date_05 date 10 0 3 True
MH_Start_Date_06 date 10 0 3 True
MH_Start_Date_07 date 10 0 3 True
MH_Start_Date_08 date 10 0 3 True
MH_Start_Date_09 date 10 0 3 True
MH_Start_Date_20 date 10 0 3 True
MH_Start_Date_21 date 10 0 3 True
MH_Start_Date_22 date 10 0 3 True
MH_Start_Date_23 date 10 0 3 True
MH_Start_Date_24 date 10 0 3 True
Patient_ID bigint 19 0 8 False

EC_Treatment

ColumnName ColumnType Precision Scale MaxLength IsNullable
AEA_Treatment_01 varchar 0 0 20 True
AEA_Treatment_02 varchar 0 0 20 True
AEA_Treatment_03 varchar 0 0 20 True
AEA_Treatment_04 varchar 0 0 20 True
AEA_Treatment_05 varchar 0 0 20 True
AEA_Treatment_06 varchar 0 0 20 True
AEA_Treatment_07 varchar 0 0 20 True
AEA_Treatment_08 varchar 0 0 20 True
AEA_Treatment_09 varchar 0 0 20 True
AEA_Treatment_10 varchar 0 0 20 True
AEA_Treatment_11 varchar 0 0 20 True
AEA_Treatment_12 varchar 0 0 20 True
AEA_Treatment_13 varchar 0 0 20 True
AEA_Treatment_14 varchar 0 0 20 True
AEA_Treatment_15 varchar 0 0 20 True
AEA_Treatment_16 varchar 0 0 20 True
AEA_Treatment_17 varchar 0 0 20 True
AEA_Treatment_18 varchar 0 0 20 True
AEA_Treatment_19 varchar 0 0 20 True
AEA_Treatment_20 varchar 0 0 20 True
AEA_Treatment_21 varchar 0 0 20 True
AEA_Treatment_22 varchar 0 0 20 True
AEA_Treatment_23 varchar 0 0 20 True
AEA_Treatment_24 varchar 0 0 20 True
EC_Ident bigint 19 0 8 False
EC_Treatment_01 varchar 0 0 20 True
EC_Treatment_02 varchar 0 0 20 True
EC_Treatment_03 varchar 0 0 20 True
EC_Treatment_04 varchar 0 0 20 True
EC_Treatment_05 varchar 0 0 20 True
EC_Treatment_06 varchar 0 0 20 True
EC_Treatment_07 varchar 0 0 20 True
EC_Treatment_08 varchar 0 0 20 True
EC_Treatment_09 varchar 0 0 20 True
EC_Treatment_10 varchar 0 0 20 True
EC_Treatment_11 varchar 0 0 20 True
EC_Treatment_12 varchar 0 0 20 True
EC_Treatment_13 varchar 0 0 20 True
EC_Treatment_14 varchar 0 0 20 True
EC_Treatment_15 varchar 0 0 20 True
EC_Treatment_16 varchar 0 0 20 True
EC_Treatment_17 varchar 0 0 20 True
EC_Treatment_18 varchar 0 0 20 True
EC_Treatment_19 varchar 0 0 20 True
EC_Treatment_20 varchar 0 0 20 True
EC_Treatment_21 varchar 0 0 20 True
EC_Treatment_22 varchar 0 0 20 True
EC_Treatment_23 varchar 0 0 20 True
EC_Treatment_24 varchar 0 0 20 True
Patient_ID bigint 19 0 8 False

NHSE_ECDS

ECDS

ColumnName ColumnType Precision Scale MaxLength IsNullable
Accessible_Information_Professional_Required_Code_Approved varchar 0 0 5 True
Accessible_Information_Professional_Required_SNOMED_CT varchar 0 0 18 True
Accommodation_Status_Code_Approved varchar 0 0 5 True
Accommodation_Status_SNOMED_CT varchar 0 0 18 True
Age_At_Arrival bigint 19 0 8 True
Age_at_CDS_Activity_Date bigint 19 0 8 True
Age_Range varchar 0 0 5 True
Arrival_Date datetime 23 3 8 True
Arrival_Mode_Approved varchar 0 0 5 True
Arrival_Month bigint 19 0 8 True
Arrival_Planned varchar 0 0 5 True
Arrival_Time varchar 0 0 8 True
Attendance_Source_Organisation varchar 0 0 9 True
Cancer_Registry varchar 0 0 5 True
CDS_Activity_Date date 10 0 3 True
CDS_Group_Indicator bigint 19 0 8 True
CDS_Interchange_Sender_Identity varchar 0 0 12 True
CDS_Prime_Recipient_Identity varchar 0 0 12 True
CDS_Type varchar 0 0 3 True
Clinical_Acuity_Code_Approved varchar 0 0 5 True
Clinical_Chief_Complaint_Code_Approved varchar 0 0 5 True
Clinical_Chief_Complaint_Extended_Code varchar 0 0 18 True
Clinical_Chief_Complaint_Injury_Related varchar 0 0 5 True
Clinical_Disease_Notification varchar 0 0 50 True
Clinical_Injury_Activity_Status_Code_Approved varchar 0 0 5 True
Clinical_Injury_Activity_Type_Code_Approved varchar 0 0 5 True
Clinical_Injury_Intent_Code_Approved varchar 0 0 5 True
Clinical_Injury_Place_Type_Approved varchar 0 0 5 True
Clinical_Trial_Code varchar 0 0 50 True
Commissioner_Code varchar 0 0 12 True
Decision_To_Admit_Receiving_Site varchar 0 0 9 True
Decision_To_Admit_Treatment_Function_Code varchar 0 0 8 True
Der_Activity_Month varchar 0 0 6 True
Der_AEA_Diagnosis_All varchar 0 0 200 True
Der_AEA_Investigation_All varchar 0 0 200 True
Der_AEA_Patient_Group varchar 0 0 2 True
Der_AEA_Treatment_All varchar 0 0 200 True
Der_Age_At_CDS_Activity_Date int 10 0 4 True
Der_Commissioner_Code varchar 0 0 5 True
Der_Dupe_Flag int 10 0 4 True
Der_EC_Arrival_Date_Time datetime 23 3 8 True
Der_EC_Departure_Date_Time datetime 23 3 8 True
Der_EC_Diagnosis_All varchar 0 0 500 True
Der_EC_Duration int 10 0 4 True
Der_EC_Investigation_All varchar 0 0 500 True
Der_EC_Treatment_All varchar 0 0 500 True
Der_Financial_Year varchar 0 0 7 False
Der_Number_AEA_Diagnosis int 10 0 4 True
Der_Number_AEA_Investigation int 10 0 4 True
Der_Number_AEA_Treatment int 10 0 4 True
Der_Number_EC_Diagnosis int 10 0 4 True
Der_Number_EC_Investigation int 10 0 4 True
Der_Number_EC_Treatment int 10 0 4 True
Der_Provider_Code varchar 0 0 5 True
Der_Provider_Site_Code varchar 0 0 5 True
Der_Record_Type varchar 0 0 4 True
Discharge_Destination_SNOMED_CT varchar 0 0 18 True
Discharge_Follow_Up_Approved varchar 0 0 5 True
Discharge_Follow_Up_SNOMED_CT varchar 0 0 18 True
Discharge_Information_Given_Approved varchar 0 0 5 True
Discharge_Information_Given_SNOMED_CT varchar 0 0 18 True
DischargeDestinationApproved varchar 0 0 5 True
DQ_Chief_Complaint_Completed varchar 0 0 5 True
DQ_Chief_Complaint_Expected varchar 0 0 5 True
DQ_Chief_Complaint_Valid varchar 0 0 5 True
DQ_Primary_Diagnosis_Completed varchar 0 0 5 True
DQ_Primary_Diagnosis_Expected varchar 0 0 5 True
DQ_Primary_Diagnosis_Valid varchar 0 0 5 True
EC_Acuity_SNOMED_CT varchar 0 0 18 True
EC_Arrival_Mode_SNOMED_CT varchar 0 0 18 True
EC_Attendance_Source_SNOMED_CT varchar 0 0 18 True
EC_AttendanceCategory varchar 0 0 1 True
EC_Chief_Complaint_SNOMED_CT varchar 0 0 18 True
EC_Conclusion_Date date 10 0 3 True
EC_Conclusion_Time varchar 0 0 8 True
EC_Conclusion_Time_Since_Arrival bigint 19 0 8 True
EC_Decision_To_Admit_Date date 10 0 3 True
EC_Decision_To_Admit_Time varchar 0 0 8 True
EC_Decision_To_Admit_Time_Since_Arrival bigint 19 0 8 True
EC_Department_Type varchar 0 0 2 True
EC_Departure_Date date 10 0 3 True
EC_Departure_Time varchar 0 0 8 True
EC_Departure_Time_Since_Arrival bigint 19 0 8 True
EC_Discharge_Status_Approved varchar 0 0 5 True
EC_Discharge_Status_SNOMED_CT varchar 0 0 18 True
EC_Ident bigint 19 0 8 True
EC_Initial_Assessment_Date date 10 0 3 True
EC_Initial_Assessment_Time varchar 0 0 8 True
EC_Initial_Assessment_Time_Since_Arrival bigint 19 0 8 True
EC_Injury_Activity_Status_SNOMED_CT varchar 0 0 18 True
EC_Injury_Activity_Type_SNOMED_CT varchar 0 0 18 True
EC_Injury_Date date 10 0 3 True
EC_Injury_Intent_SNOMED_CT varchar 0 0 18 True
EC_Injury_Mechanism_SNOMED_CT varchar 0 0 18 True
EC_Injury_Time varchar 0 0 8 True
EC_PCD_Indicator int 10 0 4 True
EC_Place_Of_Injury_Latitude varchar 0 0 10 True
EC_Place_Of_Injury_Longitude varchar 0 0 10 True
EC_Place_Of_Injury_SNOMED_CT varchar 0 0 18 True
EC_Seen_For_Treatment_Date date 10 0 3 True
EC_Seen_For_Treatment_Time varchar 0 0 8 True
EC_Seen_For_Treatment_Time_Since_Arrival bigint 19 0 8 True
Ethnic_Category varchar 0 0 1 True
Exclusions varchar 0 0 100 True
Finished_Indicator bigint 19 0 8 True
Fractional_Age_At_Arrival float 53 0 8 True
HES_Age_At_Arrival bigint 19 0 8 True
HES_Age_At_Departure bigint 19 0 8 True
Index_Of_Multiple_Deprivation_Decile varchar 0 0 50 True
Index_Of_Multiple_Deprivation_Decile_Description varchar 0 0 50 True
Interpreter_Language_Code_Approved varchar 0 0 5 True
Interpreter_Language_SNOMED_CT varchar 0 0 18 True
Month_of_Birth varchar 0 0 2 True
NHS_Number_Is_Valid varchar 0 0 5 True
NHS_Number_Status_Indicator_Code varchar 0 0 2 True
Overseas_Visitor_Charging_Category varchar 0 0 1 True
Patient_ID bigint 19 0 8 False
Patient_Type varchar 0 0 4 True
Preferred_Spoken_Language_Code_Approved varchar 0 0 5 True
Preferred_Spoken_Language_SNOMED_CT varchar 0 0 18 True
Provider_Code varchar 0 0 12 True
Reason_For_Access varchar 0 0 100 True
RTT_Period_End_Date date 10 0 3 True
RTT_Period_Length bigint 19 0 8 True
RTT_Period_Start_Date date 10 0 3 True
RTT_Period_Status varchar 0 0 2 True
Rural_Urban_Indicator varchar 0 0 1 True
Sex varchar 0 0 5 True
Site_Code_of_Treatment varchar 0 0 5 True
SNOMED_Version varchar 0 0 6 True
SUS_Code_Cleaning_Applied varchar 0 0 5 True
SUS_Costing_Period varchar 0 0 20 True
SUS_Excluded varchar 0 0 5 True
SUS_Final_Price numeric 38 38 9 True
SUS_Grouper_Version varchar 0 0 8 True
SUS_HRG_Code varchar 0 0 5 True
SUS_MFAdjustment numeric 38 38 9 True
SUS_MFF varchar 0 0 10 True
SUS_Tariff numeric 38 38 9 True
SUS_Tariff_Description varchar 0 0 10 True
Wait_Time_Measurement_Type varchar 0 0 2 True
Withheld_Identity_Reason varchar 0 0 2 True
Year_of_Birth varchar 0 0 4 True

ECDS_EC_Diagnoses

ColumnName ColumnType Precision Scale MaxLength IsNullable
DiagnosisCode varchar 0 0 50 True
EC_Ident bigint 19 0 8 True
Ordinal int 10 0 4 False
Patient_ID bigint 19 0 8 False

NHSE_HEALTH_CARE_WORKER

HealthCareWorker

ColumnName ColumnType Precision Scale MaxLength IsNullable
HealthCareWorker varchar 0 0 10 False
Patient_ID bigint 19 0 8 False

NHSE_MPI

MPI

ColumnName ColumnType Precision Scale MaxLength IsNullable
Birth_Month varchar 0 0 10 True
Care_Home_Flag varchar 0 0 10 True
Data_Source varchar 0 0 100 True
Date_Added date 10 0 3 True
DateFrom date 10 0 3 True
DateTo date 10 0 3 True
Death_Month varchar 0 0 10 True
Gender varchar 0 0 1 True
Latest_Flag varchar 0 0 10 True
Living_Alone_Flag varchar 0 0 100 True
Living_with_elderly_Flag varchar 0 0 100 True
Living_with_young_Flag varchar 0 0 100 True
Original_Posting_Date date 10 0 3 True
OS_Property_Classification varchar 0 0 100 True
Patient_ID bigint 19 0 8 False
private_outdoor_space varchar 0 0 100 True
private_outdoor_space_area varchar 0 0 100 True
property_type varchar 0 0 100 True
Pseudo_parent_uprn varchar 0 0 200 True
Pseudo_uprn varchar 0 0 200 True
RP_of_Death varchar 0 0 10 True
Rural_Urban_Classification varchar 0 0 100 True
ServiceType varchar 0 0 100 True

UPRN

ColumnName ColumnType Precision Scale MaxLength IsNullable
_0to4 int 10 0 4 True
_10to14 int 10 0 4 True
_15to19 int 10 0 4 True
_20to24 int 10 0 4 True
_25to29 int 10 0 4 True
_30to34 int 10 0 4 True
_40to44 int 10 0 4 True
_45to49 int 10 0 4 True
_50to54 int 10 0 4 True
_55to59 int 10 0 4 True
_5to9 int 10 0 4 True
_60to64 int 10 0 4 True
_65to69 int 10 0 4 True
_70to74 int 10 0 4 True
_75to79 int 10 0 4 True
_80to84 int 10 0 4 True
_85Plus int 10 0 4 True
Care_Home_Flag varchar 0 0 100 True
class varchar 0 0 100 True
private_outdoor_space varchar 0 0 100 True
private_outdoor_space_area varchar 0 0 100 True
property_type varchar 0 0 100 True
Pseudo_parent_uprn varchar 0 0 200 True
Pseudo_uprn varchar 0 0 200 True
Rural_Urban_Classification varchar 0 0 100 True
ServiceType varchar 0 0 100 True
Total_Pop int 10 0 4 True

NHSE_OPA

OPA

ColumnName ColumnType Precision Scale MaxLength IsNullable
Activity_Location_Type_Code varchar 0 0 5 True
Administrative_Category varchar 0 0 2 True
Appointment_Date date 10 0 3 True
Attendance_Status varchar 0 0 2 True
Clinic_Code varchar 0 0 100 True
Consultation_Medium_Used varchar 0 0 2 True
Der_Activity_Month varchar 0 0 100 True
Der_Financial_Year varchar 0 0 100 True
Ethnic_Category varchar 0 0 2 True
First_Attendance varchar 0 0 2 True
HRG_Code varchar 0 0 10 True
HRG_Version_No varchar 0 0 10 True
Main_Specialty_Code varchar 0 0 3 True
Medical_Staff_Type_Seeing_Patient varchar 0 0 2 True
MultiProf_Ind_Code varchar 0 0 2 True
OPA_Ident bigint 19 0 8 False
OPA_Referral_Source varchar 0 0 2 True
Operation_Status varchar 0 0 2 True
Outcome_of_Attendance varchar 0 0 2 True
Patient_ID bigint 19 0 8 False
Priority_Type varchar 0 0 2 True
Provider_Code varchar 0 0 100 True
Provider_Code_Type varchar 0 0 100 True
Referral_Request_Received_Date date 10 0 3 True
Treatment_Function_Code varchar 0 0 3 True

OPA_Cost

ColumnName ColumnType Precision Scale MaxLength IsNullable
Grand_Total_Payment_MFF real 24 0 4 True
OPA_Ident bigint 19 0 8 False
Patient_ID bigint 19 0 8 False
Tariff_OPP real 24 0 4 True
Tariff_Total_Payment real 24 0 4 True

OPA_Diag

ColumnName ColumnType Precision Scale MaxLength IsNullable
OPA_Ident bigint 19 0 8 False
Patient_ID bigint 19 0 8 False
Primary_Diagnosis_Code varchar 0 0 100 True
Primary_Diagnosis_Code_Read varchar 0 0 5 True
Secondary_Diagnosis_Code_1 varchar 0 0 100 True
Secondary_Diagnosis_Code_1_Read varchar 0 0 5 True

OPA_Proc

ColumnName ColumnType Precision Scale MaxLength IsNullable
OPA_Ident bigint 19 0 8 False
Patient_ID bigint 19 0 8 False
Primary_Procedure_Code varchar 0 0 100 True
Primary_Procedure_Code_Read varchar 0 0 5 True
Procedure_Code_2 varchar 0 0 100 True
Procedure_Code_2_Read varchar 0 0 5 True

NHSE_SGSS

SGSS_AllTests_Negative

ColumnName ColumnType Precision Scale MaxLength IsNullable
Age_In_Years int 10 0 4 True
County_Description varchar 0 0 50 True
Ethnic_Category_Desc varchar 0 0 255 True
Lab_Report_Date date 10 0 3 True
LFT_Flag varchar 0 0 255 True
Organism_Species_Name varchar 0 0 200 True
Patient_ID bigint 19 0 8 False
Patient_Sex varchar 0 0 50 True
Pillar varchar 0 0 255 True
PostCode_Source varchar 0 0 50 True
Specimen_Date date 10 0 3 True
Symptomatic varchar 0 0 50 True

SGSS_AllTests_Positive

ColumnName ColumnType Precision Scale MaxLength IsNullable
Age_In_Years int 10 0 4 True
County_Description varchar 0 0 50 True
Ethnic_Category_Desc varchar 0 0 255 True
Lab_Report_Date date 10 0 3 True
LFT_Flag varchar 0 0 255 True
Organism_Species_Name varchar 0 0 200 True
Patient_ID bigint 19 0 8 False
Patient_Sex varchar 0 0 50 True
Pillar varchar 0 0 255 True
PostCode_Source varchar 0 0 50 True
SGTF varchar 0 0 255 True
Specimen_Date date 10 0 3 True
Symptomatic varchar 0 0 50 True
Variant varchar 0 0 255 True
VariantDetectionMethod varchar 0 0 255 True

SGSS_Negative

ColumnName ColumnType Precision Scale MaxLength IsNullable
Age_In_Years int 10 0 4 True
County_Description varchar 0 0 50 True
Earliest_Specimen_Date date 10 0 3 True
Lab_Report_Date date 10 0 3 True
Organism_Species_Name varchar 0 0 200 True
Patient_ID bigint 19 0 8 False
Patient_Sex varchar 0 0 50 True
PostCode_Source varchar 0 0 50 True

SGSS_Positive

ColumnName ColumnType Precision Scale MaxLength IsNullable
Age_In_Years int 10 0 4 True
CaseCategory varchar 0 0 50 False
County_Description varchar 0 0 50 True
Earliest_Specimen_Date date 10 0 3 True
Lab_Report_Date date 10 0 3 True
Organism_Species_Name varchar 0 0 200 True
Patient_ID bigint 19 0 8 False
Patient_Sex varchar 0 0 50 True
PostCode_Source varchar 0 0 50 True
SGTF varchar 0 0 10 False

NHSE_Therapeutics

Therapeutics

ColumnName ColumnType Precision Scale MaxLength IsNullable
AgeAtReceivedDate int 10 0 4 True
CASIM05_date_of_symptom_onset varchar 0 0 1000 True
CASIM05_risk_cohort varchar 0 0 1000 True
Count int 10 0 4 True
COVID_indication varchar 0 0 1000 True
CurrentStatus varchar 0 0 1000 True
Der_LoadDate varchar 0 0 1000 True
Diagnosis varchar 0 0 1000 True
FormName varchar 0 0 1000 True
Intervention varchar 0 0 1000 True
MOL1_high_risk_cohort varchar 0 0 1000 True
MOL1_onset_of_symptoms varchar 0 0 1000 True
Patient_ID bigint 19 0 8 False
Received datetime 23 3 8 True
Region varchar 0 0 1000 True
SOT02_onset_of_symptoms varchar 0 0 1000 True
SOT02_risk_cohorts varchar 0 0 1000 True
TreatmentStartDate datetime 23 3 8 True

NHSE_WL

WL_ClockStops

ColumnName ColumnType Precision Scale MaxLength IsNullable
ACTIVITY_TREATMENT_FUNCTION_CODE varchar 0 0 1000 True
ADMISSION_METHOD_CODE_HOSPITAL_PROVIDER_SPELL varchar 0 0 1000 True
Cancellation_Date varchar 0 0 1000 True
Completed_Type varchar 0 0 1000 True
Date_Last_Attended varchar 0 0 1000 True
Date_Of_Last_Priority_Review varchar 0 0 1000 True
DECISION_TO_ADMIT_DATE varchar 0 0 1000 True
Diagnostic_Priority_Code varchar 0 0 1000 True
Due_Date varchar 0 0 1000 True
Inclusion_On_Cancer_Ptl varchar 0 0 1000 True
Last_Dna_Date varchar 0 0 1000 True
Local_Outcome_Of_Attendance varchar 0 0 1000 True
Local_Rtt_Status_Code varchar 0 0 1000 True
MAIN_SPECIALTY_CODE varchar 0 0 1000 True
OPCS_Code varchar 0 0 1000 True
OUTCOME_OF_ATTENDANCE_CODE varchar 0 0 1000 True
Outpatient_Appointment_Date varchar 0 0 1000 True
Outpatient_Future_Appointment_Date varchar 0 0 1000 True
Outpatient_Priority_Code varchar 0 0 1000 True
Patient_ID bigint 19 0 8 False
PRIORITY_TYPE_CODE varchar 0 0 1000 True
Procedure_Priority_Code varchar 0 0 1000 True
PSEUDO_ORGANISATION_CODE_PATIENT_PATHWAY_IDENTIFIER_ISSUER varbinary 0 0 8000 True
PSEUDO_ORGANISATION_IDENTIFIER_CODE_OF_COMMISSIONER varbinary 0 0 8000 True
PSEUDO_ORGANISATION_IDENTIFIER_CODE_OF_PROVIDER varbinary 0 0 8000 True
PSEUDO_ORGANISATION_SITE_IDENTIFIER_OF_TREATMENT varbinary 0 0 8000 True
PSEUDO_PATIENT_PATHWAY_IDENTIFIER varbinary 0 0 8000 True
Pseudo_Referral_Identifier varbinary 0 0 8000 True
Referral_Request_Received_Date varchar 0 0 1000 True
REFERRAL_TO_TREATMENT_PERIOD_END_DATE varchar 0 0 1000 True
REFERRAL_TO_TREATMENT_PERIOD_START_DATE varchar 0 0 1000 True
REFERRAL_TO_TREATMENT_PERIOD_STATUS varchar 0 0 1000 True
SOURCE_OF_REFERRAL_FOR_OUTPATIENTS varchar 0 0 1000 True
Tci_Date varchar 0 0 1000 True
Waiting_List_Type varchar 0 0 1000 True
Week_Ending_Date varchar 0 0 1000 True

WL_Diagnostics

ColumnName ColumnType Precision Scale MaxLength IsNullable
Diagnostic_Clock_Start_Date varchar 0 0 1000 True
Diagnostic_Modality varchar 0 0 1000 True
Diagnostic_Priority_Code varchar 0 0 1000 True
Inclusion_On_Cancer_Ptl varchar 0 0 1000 True
Patient_ID bigint 19 0 8 False
Planned_Diagnostic_Due_Date varchar 0 0 1000 True
PRIORITY_TYPE_CODE varchar 0 0 1000 True
Proposed_Procedure_Opcs_Code varchar 0 0 1000 True
PSEUDO_ORGANISATION_CODE_PATIENT_PATHWAY_IDENTIFIER_ISSUER varbinary 0 0 8000 True
PSEUDO_ORGANISATION_IDENTIFIER_CODE_OF_COMMISSIONER varbinary 0 0 8000 True
PSEUDO_ORGANISATION_IDENTIFIER_CODE_OF_PROVIDER varbinary 0 0 8000 True
PSEUDO_ORGANISATION_SITE_IDENTIFIER_OF_TREATMENT varbinary 0 0 8000 True
PSEUDO_PATIENT_PATHWAY_IDENTIFIER varbinary 0 0 8000 True
Pseudo_Referral_Identifier varbinary 0 0 8000 True
REFERRAL_TO_TREATMENT_PERIOD_END_DATE varchar 0 0 1000 True
REFERRAL_TO_TREATMENT_PERIOD_START_DATE varchar 0 0 1000 True
Waiting_List_Type varchar 0 0 1000 True
Week_Ending_Date varchar 0 0 1000 True

WL_OpenPathways

ColumnName ColumnType Precision Scale MaxLength IsNullable
ACTIVITY_TREATMENT_FUNCTION_CODE varchar 0 0 1000 True
ADMISSION_METHOD_CODE_HOSPITAL_PROVIDER_SPELL varchar 0 0 1000 True
Cancellation_Date varchar 0 0 1000 True
Current_Pathway_Period_Start_Date varchar 0 0 1000 True
Date_Last_Attended varchar 0 0 1000 True
Date_Of_Last_Priority_Review varchar 0 0 1000 True
DECISION_TO_ADMIT_DATE varchar 0 0 1000 True
Diagnostic_Priority_Code varchar 0 0 1000 True
Due_Date varchar 0 0 1000 True
Inclusion_On_Cancer_Ptl varchar 0 0 1000 True
Last_Dna_Date varchar 0 0 1000 True
Local_Outcome_Of_Attendance varchar 0 0 1000 True
Local_Rtt_Status_Code varchar 0 0 1000 True
MAIN_SPECIALTY_CODE varchar 0 0 1000 True
OUTCOME_OF_ATTENDANCE_CODE varchar 0 0 1000 True
Outpatient_Appointment_Date varchar 0 0 1000 True
Outpatient_Future_Appointment_Date varchar 0 0 1000 True
Outpatient_Priority_Code varchar 0 0 1000 True
Patient_ID bigint 19 0 8 False
PRIORITY_TYPE_CODE varchar 0 0 1000 True
Procedure_Priority_Code varchar 0 0 1000 True
Proposed_Procedure_Opcs_Code varchar 0 0 1000 True
PSEUDO_ORGANISATION_CODE_PATIENT_PATHWAY_IDENTIFIER_ISSUER varbinary 0 0 8000 True
PSEUDO_ORGANISATION_IDENTIFIER_CODE_OF_COMMISSIONER varbinary 0 0 8000 True
PSEUDO_ORGANISATION_IDENTIFIER_CODE_OF_PROVIDER varbinary 0 0 8000 True
PSEUDO_ORGANISATION_SITE_IDENTIFIER_OF_TREATMENT varbinary 0 0 8000 True
PSEUDO_PATIENT_PATHWAY_IDENTIFIER varbinary 0 0 8000 True
Pseudo_Referral_Identifier varbinary 0 0 8000 True
REFERRAL_REQUEST_RECEIVED_DATE varchar 0 0 1000 True
REFERRAL_TO_TREATMENT_PERIOD_END_DATE varchar 0 0 1000 True
REFERRAL_TO_TREATMENT_PERIOD_START_DATE varchar 0 0 1000 True
REFERRAL_TO_TREATMENT_PERIOD_STATUS varchar 0 0 1000 True
SOURCE_OF_REFERRAL varchar 0 0 1000 True
Tci_Date varchar 0 0 1000 True
Waiting_List_Type varchar 0 0 1000 True
Week_Ending_Date varchar 0 0 1000 True

ONS_CIS

ONS_CIS

ColumnName ColumnType Precision Scale MaxLength IsNullable
age_at_visit int 10 0 4 True
contact_any_covid bit 1 0 1 True
contact_any_covid_dayssinceg real 24 0 4 True
contact_face_covering int 10 0 4 True
country int 10 0 4 True
covid_admitted bit 1 0 1 True
covid_date date 10 0 3 True
covid_nhs_contact bit 1 0 1 True
covid_test_blood bit 1 0 1 True
covid_test_blood_neg_last_date date 10 0 3 True
covid_test_blood_pos_first_date date 10 0 3 True
covid_test_blood_result int 10 0 4 True
covid_test_swab bit 1 0 1 True
covid_test_swab_neg_last_date date 10 0 3 True
covid_test_swab_pos_first_date date 10 0 3 True
covid_test_swab_result int 10 0 4 True
covid_think_havehad bit 1 0 1 True
ct_mean varchar 0 0 100 True
dataset int 10 0 4 True
dvhsize int 10 0 4 True
ethnicity int 10 0 4 True
geography_code varchar 0 0 100 True
geography_name varchar 0 0 100 True
gor9d varchar 0 0 100 True
gor9d_surge varchar 0 0 100 True
health_care_clean bit 1 0 1 True
health_conditions bit 1 0 1 True
health_conditions_impact int 10 0 4 True
hhsize int 10 0 4 True
imp_age int 10 0 4 True
imp_sex varchar 0 0 10 True
n_participants int 10 0 4 True
patient_facing_clean int 10 0 4 True
Patient_ID bigint 19 0 8 False
received_ox_date date 10 0 3 True
result_combined varchar 0 0 100 True
result_mk varchar 0 0 100 True
result_mk_date date 10 0 3 True
result_tdi varchar 0 0 100 True
samples_taken_date date 10 0 3 True
school_year varchar 0 0 100 True
self_isolating bit 1 0 1 True
self_isolating_v1 int 10 0 4 True
sex varchar 0 0 10 True
smoke_ever_regularly bit 1 0 1 True
smoke_now_cigar bit 1 0 1 True
smoke_now_cigarettes bit 1 0 1 True
smoke_now_pipe bit 1 0 1 True
smoke_now_vape bit 1 0 1 True
SOC_occupation varchar 0 0 100 True
surge_flag bit 1 0 1 True
sympt_covid_abdominal_pain bit 1 0 1 True
sympt_covid_any bit 1 0 1 True
sympt_covid_cough bit 1 0 1 True
sympt_covid_diarrhoea bit 1 0 1 True
sympt_covid_fatigue_weakness bit 1 0 1 True
sympt_covid_fever bit 1 0 1 True
sympt_covid_headache bit 1 0 1 True
sympt_covid_loss_of_smell bit 1 0 1 True
sympt_covid_loss_of_taste bit 1 0 1 True
sympt_covid_muscle_ache_myalgia bit 1 0 1 True
sympt_covid_nausea_vomiting bit 1 0 1 True
sympt_covid_shortness_of_breath bit 1 0 1 True
sympt_covid_sore_throat bit 1 0 1 True
sympt_now_abdominal_pain bit 1 0 1 True
sympt_now_any bit 1 0 1 True
sympt_now_cough bit 1 0 1 True
sympt_now_date date 10 0 3 True
sympt_now_diarrhoea bit 1 0 1 True
sympt_now_fatigue_weakness bit 1 0 1 True
sympt_now_fever bit 1 0 1 True
sympt_now_headache bit 1 0 1 True
sympt_now_loss_of_smell bit 1 0 1 True
sympt_now_loss_of_taste bit 1 0 1 True
sympt_now_muscle_ache_myalgia bit 1 0 1 True
sympt_now_nausea_vomiting bit 1 0 1 True
sympt_now_shortness_of_breath bit 1 0 1 True
sympt_now_sore_throat bit 1 0 1 True
tenure_group int 10 0 4 True
think_have_covid_sympt_now bit 1 0 1 True
travel_abroad bit 1 0 1 True
travel_abroad_date date 10 0 3 True
visit_date date 10 0 3 True
visit_id varchar 0 0 100 True
work_direct_contact_patients_etc bit 1 0 1 True
work_location int 10 0 4 True
work_outside_home_days real 24 0 4 True
work_sector int 10 0 4 True
work_social_distancing int 10 0 4 True
work_status int 10 0 4 True
work_status_clean int 10 0 4 True
work_status_v1 int 10 0 4 True
work_travel int 10 0 4 True

ONS_DEATHS

ONS_Deaths

ColumnName ColumnType Precision Scale MaxLength IsNullable
ageinyrs int 10 0 4 True
dod date 10 0 3 True
FIC10MEN1 varchar 0 0 100 True
FIC10MEN10 varchar 0 0 100 True
FIC10MEN11 varchar 0 0 100 True
FIC10MEN12 varchar 0 0 100 True
FIC10MEN13 varchar 0 0 100 True
FIC10MEN14 varchar 0 0 100 True
FIC10MEN15 varchar 0 0 100 True
FIC10MEN2 varchar 0 0 100 True
FIC10MEN3 varchar 0 0 100 True
FIC10MEN4 varchar 0 0 100 True
FIC10MEN5 varchar 0 0 100 True
FIC10MEN6 varchar 0 0 100 True
FIC10MEN7 varchar 0 0 100 True
FIC10MEN8 varchar 0 0 100 True
FIC10MEN9 varchar 0 0 100 True
FIC10UND varchar 0 0 100 True
ICD10001 varchar 0 0 100 True
ICD10002 varchar 0 0 100 True
ICD10003 varchar 0 0 100 True
ICD10004 varchar 0 0 100 True
ICD10005 varchar 0 0 100 True
ICD10006 varchar 0 0 100 True
ICD10007 varchar 0 0 100 True
ICD10008 varchar 0 0 100 True
ICD10009 varchar 0 0 100 True
ICD10010 varchar 0 0 100 True
ICD10011 varchar 0 0 100 True
ICD10012 varchar 0 0 100 True
ICD10013 varchar 0 0 100 True
ICD10014 varchar 0 0 100 True
ICD10015 varchar 0 0 100 True
icd10u varchar 0 0 100 True
Patient_ID bigint 19 0 8 False
Place_of_occurrence varchar 0 0 1000 True
sex varchar 0 0 10 True

OS_BUILD

BuildInfo

ColumnName ColumnType Precision Scale MaxLength IsNullable
BuildDate datetime 23 3 8 False
BuildDesc varchar 0 0 100 False
BuildNumber int 10 0 4 False

CodeCountIndicator

ColumnName ColumnType Precision Scale MaxLength IsNullable
CodeCountIndicator float 53 0 8 True
CTV3Code varchar 0 0 50 False

LatestBuildTime

ColumnName ColumnType Precision Scale MaxLength IsNullable
DtLatestBuild datetime 23 3 8 False

OpenSAFELYSchemaInformation

ColumnName ColumnType Precision Scale MaxLength IsNullable
CollationName nvarchar 0 0 256 True
CollationName sysname 0 0 256 True
ColumnId int 10 0 4 False
ColumnName nvarchar 0 0 256 True
ColumnName sysname 0 0 256 True
ColumnType nvarchar 0 0 256 False
ColumnType sysname 0 0 256 False
DataSource varchar 0 0 1000 True
IsNullable bit 1 0 1 True
MaxLength smallint 5 0 2 False
Precision tinyint 3 0 1 False
Scale tinyint 3 0 1 False
TableName nvarchar 0 0 256 False
TableName sysname 0 0 256 False

OS_DERIVED

Household

ColumnName ColumnType Precision Scale MaxLength IsNullable
CareHome bit 1 0 1 True
Household_ID bigint 19 0 8 True
HouseholdSize int 10 0 4 True
MatchesUprnCount bit 1 0 1 True
MixedSoftwareHousehold bit 1 0 1 True
MSOA varchar 0 0 50 True
NFA_Unknown bit 1 0 1 True
Prison bit 1 0 1 True
TppPercentage int 10 0 4 True

HouseholdMember

ColumnName ColumnType Precision Scale MaxLength IsNullable
Household_ID bigint 19 0 8 False
HouseholdMember_ID bigint 19 0 8 False
Patient_ID bigint 19 0 8 False

MSOA_PopulationEstimates_2019

ColumnName ColumnType Precision Scale MaxLength IsNullable
Age_0 int 10 0 4 False
Age_1 int 10 0 4 False
Age_10 int 10 0 4 False
Age_11 int 10 0 4 False
Age_12 int 10 0 4 False
Age_13 int 10 0 4 False
Age_14 int 10 0 4 False
Age_15 int 10 0 4 False
Age_16 int 10 0 4 False
Age_17 int 10 0 4 False
Age_18 int 10 0 4 False
Age_19 int 10 0 4 False
Age_2 int 10 0 4 False
Age_20 int 10 0 4 False
Age_21 int 10 0 4 False
Age_22 int 10 0 4 False
Age_23 int 10 0 4 False
Age_24 int 10 0 4 False
Age_25 int 10 0 4 False
Age_26 int 10 0 4 False
Age_27 int 10 0 4 False
Age_28 int 10 0 4 False
Age_29 int 10 0 4 False
Age_3 int 10 0 4 False
Age_30 int 10 0 4 False
Age_31 int 10 0 4 False
Age_32 int 10 0 4 False
Age_33 int 10 0 4 False
Age_34 int 10 0 4 False
Age_35 int 10 0 4 False
Age_36 int 10 0 4 False
Age_37 int 10 0 4 False
Age_38 int 10 0 4 False
Age_39 int 10 0 4 False
Age_4 int 10 0 4 False
Age_40 int 10 0 4 False
Age_41 int 10 0 4 False
Age_42 int 10 0 4 False
Age_43 int 10 0 4 False
Age_44 int 10 0 4 False
Age_45 int 10 0 4 False
Age_46 int 10 0 4 False
Age_47 int 10 0 4 False
Age_48 int 10 0 4 False
Age_49 int 10 0 4 False
Age_5 int 10 0 4 False
Age_50 int 10 0 4 False
Age_51 int 10 0 4 False
Age_52 int 10 0 4 False
Age_53 int 10 0 4 False
Age_54 int 10 0 4 False
Age_55 int 10 0 4 False
Age_56 int 10 0 4 False
Age_57 int 10 0 4 False
Age_58 int 10 0 4 False
Age_59 int 10 0 4 False
Age_6 int 10 0 4 False
Age_60 int 10 0 4 False
Age_61 int 10 0 4 False
Age_62 int 10 0 4 False
Age_63 int 10 0 4 False
Age_64 int 10 0 4 False
Age_65 int 10 0 4 False
Age_66 int 10 0 4 False
Age_67 int 10 0 4 False
Age_68 int 10 0 4 False
Age_69 int 10 0 4 False
Age_7 int 10 0 4 False
Age_70 int 10 0 4 False
Age_71 int 10 0 4 False
Age_72 int 10 0 4 False
Age_73 int 10 0 4 False
Age_74 int 10 0 4 False
Age_75 int 10 0 4 False
Age_76 int 10 0 4 False
Age_77 int 10 0 4 False
Age_78 int 10 0 4 False
Age_79 int 10 0 4 False
Age_8 int 10 0 4 False
Age_80 int 10 0 4 False
Age_81 int 10 0 4 False
Age_82 int 10 0 4 False
Age_83 int 10 0 4 False
Age_84 int 10 0 4 False
Age_85 int 10 0 4 False
Age_86 int 10 0 4 False
Age_87 int 10 0 4 False
Age_88 int 10 0 4 False
Age_89 int 10 0 4 False
Age_9 int 10 0 4 False
Age_90_Plus int 10 0 4 False
Age_All int 10 0 4 False
LA_Code_2019 varchar 0 0 50 False
LA_Code_2020 varchar 0 0 50 False
MSOA_Code varchar 0 0 50 False

PotentialCareHomeAddress

ColumnName ColumnType Precision Scale MaxLength IsNullable
LocationDoesNotRequireNursing varchar 0 0 5 False
LocationRequiresNursing varchar 0 0 5 False
Patient_ID bigint 19 0 8 False
PatientAddress_ID bigint 19 0 8 False

S1

Appointment

ColumnName ColumnType Precision Scale MaxLength IsNullable
Appointment_ID bigint 19 0 8 False
ArrivedDate datetime 23 3 8 False
BookedDate datetime 23 3 8 False
EndDate datetime 23 3 8 False
FinishedDate datetime 23 3 8 False
Organisation_ID bigint 19 0 8 False
Patient_ID bigint 19 0 8 False
SeenDate datetime 23 3 8 False
StartDate datetime 23 3 8 False
Status int 10 0 4 False

CTV3Dictionary

ColumnName ColumnType Precision Scale MaxLength IsNullable
CTV3Code varchar 0 0 50 False
Description varchar 0 0 255 False

CTV3Hierarchy

ColumnName ColumnType Precision Scale MaxLength IsNullable
ChildCTV3Code varchar 0 0 50 False
ChildCTV3Description varchar 0 0 255 False
ChildToParentDistance int 10 0 4 False
ParentCTV3Code varchar 0 0 50 False
ParentCTV3Description varchar 0 0 255 False

CodedEvent

ColumnName ColumnType Precision Scale MaxLength IsNullable
CodedEvent_ID bigint 19 0 8 False
Consultation_ID bigint 19 0 8 False
ConsultationDate datetime 23 3 8 False
CTV3Code varchar 0 0 50 False
NumericValue real 24 0 4 False
Patient_ID bigint 19 0 8 False

CodedEventRange

ColumnName ColumnType Precision Scale MaxLength IsNullable
CodedEvent_ID bigint 19 0 8 False
CodedEventRange_ID bigint 19 0 8 False
Comparator tinyint 3 0 1 False
Consultation_ID bigint 19 0 8 False
LowerBound real 24 0 4 False
Patient_ID int 10 0 4 False
UpperBound real 24 0 4 False

Consultation

ColumnName ColumnType Precision Scale MaxLength IsNullable
Consultation_ID bigint 19 0 8 False
ConsultationDate datetime 23 3 8 False
Patient_ID bigint 19 0 8 False
Registration_ID bigint 19 0 8 False

DataDictionary

ColumnName ColumnType Precision Scale MaxLength IsNullable
Code varchar 0 0 255 True
Description varchar 0 0 1000 True
Table varchar 0 0 1000 True
Type varchar 0 0 255 True

ICD10Dictionary

ColumnName ColumnType Precision Scale MaxLength IsNullable
Code varchar 0 0 4 False
CodeDescription varchar 0 0 500 False
ParentCode char 0 0 3 False
ParentCodeDescription varchar 0 0 500 False

MedicationDictionary

ColumnName ColumnType Precision Scale MaxLength IsNullable
CompanyName varchar 0 0 200 True
DMD_ID varchar 0 0 50 True
Form varchar 0 0 50 True
FullName varchar 0 0 1000 True
MultilexDrug_ID varchar 0 0 767 True
PackDescription varchar 0 0 50 True
ProductId bigint 19 0 8 False
RootName varchar 0 0 100 True
Strength varchar 0 0 500 True

MedicationIssue

ColumnName ColumnType Precision Scale MaxLength IsNullable
Consultation_ID bigint 19 0 8 False
ConsultationDate datetime 23 3 8 False
Dose varchar 0 0 255 False
EndDate datetime 23 3 8 False
MedicationIssue_ID bigint 19 0 8 False
MedicationStatus int 10 0 4 False
MultilexDrug_ID varchar 0 0 255 False
Patient_ID bigint 19 0 8 False
Quantity varchar 0 0 255 False
RepeatMedication_ID bigint 19 0 8 False
StartDate datetime 23 3 8 False

MedicationRepeat

ColumnName ColumnType Precision Scale MaxLength IsNullable
Consultation_ID bigint 19 0 8 False
ConsultationDate datetime 23 3 8 False
Dose varchar 0 0 255 False
EndDate datetime 23 3 8 False
MedicationRepeat_ID bigint 19 0 8 False
MedicationStatus int 10 0 4 False
MultilexDrug_ID varchar 0 0 255 False
Patient_ID bigint 19 0 8 False
Quantity varchar 0 0 255 False
StartDate datetime 23 3 8 False

MedicationSensitivity

ColumnName ColumnType Precision Scale MaxLength IsNullable
Consultation_ID bigint 19 0 8 False
ConsultationDate datetime 23 3 8 False
Ended bit 1 0 1 True
FormulationSpecific bit 1 0 1 False
MedicationSensitivity_ID int 10 0 4 False
MultilexDrug_ID varchar 0 0 100 False
Patient_ID int 10 0 4 False
StartDate datetime 23 3 8 False

Organisation

ColumnName ColumnType Precision Scale MaxLength IsNullable
GoLiveDate datetime 23 3 8 False
MSOACode varchar 0 0 150 False
Organisation_ID bigint 19 0 8 False
Region varchar 0 0 255 False
STPCode varchar 0 0 50 False

Patient

ColumnName ColumnType Precision Scale MaxLength IsNullable
DateOfBirth date 10 0 3 True
DateOfDeath date 10 0 3 True
Patient_ID bigint 19 0 8 False
Sex char 0 0 1 False

PatientAddress

ColumnName ColumnType Precision Scale MaxLength IsNullable
AddressType int 10 0 4 False
EndDate datetime 23 3 8 False
ImdRankRounded int 10 0 4 False
MSOACode varchar 0 0 150 False
Patient_ID bigint 19 0 8 False
PatientAddress_ID bigint 19 0 8 False
RuralUrbanClassificationCode int 10 0 4 False
StartDate datetime 23 3 8 False

QOFClusterReference

ColumnName ColumnType Precision Scale MaxLength IsNullable
ClusterName varchar 0 0 100 False
ClusterType varchar 0 0 50 False
CTV3Code varchar 0 0 50 False
Description varchar 0 0 255 False

RegistrationHistory

ColumnName ColumnType Precision Scale MaxLength IsNullable
EndDate datetime 23 3 8 False
Organisation_ID bigint 19 0 8 False
Patient_ID bigint 19 0 8 False
Registration_ID bigint 19 0 8 False
StartDate datetime 23 3 8 False

UnitDictionary

ColumnName ColumnType Precision Scale MaxLength IsNullable
CTV3Code varchar 0 0 50 False
DecimalPlaces int 10 0 4 False
LowerNormalBound real 24 0 4 False
Maximum real 24 0 4 False
Minimum real 24 0 4 False
UnitDictionary_ID int 10 0 4 False
Units varchar 0 0 50 False
UpperNormalBound real 24 0 4 False

Vaccination

ColumnName ColumnType Precision Scale MaxLength IsNullable
Patient_ID bigint 19 0 8 False
Vaccination_ID bigint 19 0 8 False
VaccinationDate datetime 23 3 8 False
VaccinationName varchar 0 0 100 False
VaccinationName_ID bigint 19 0 8 False
VaccinationSchedulePart int 10 0 4 False

VaccinationReference

ColumnName ColumnType Precision Scale MaxLength IsNullable
VaccinationContent varchar 0 0 50 False
VaccinationName varchar 0 0 100 False
VaccinationName_ID int 10 0 4 False

YCodeToSnomedMapping

ColumnName ColumnType Precision Scale MaxLength IsNullable
SctConceptId bigint 19 0 8 True
YCode varchar 0 0 5 False

UK_Renal_Register

UKRR

ColumnName ColumnType Precision Scale MaxLength IsNullable
creat real 24 0 4 True
dataset varchar 0 0 1000 True
eGFR_ckdepi real 24 0 4 True
mod_prev varchar 0 0 1000 True
mod_start varchar 0 0 1000 True
Patient_ID bigint 19 0 8 False
renal_centre varchar 0 0 1000 True
rrt_start date 10 0 3 True