# Dataset schema in OpenSAFELY-TPP¶

This notebook displays the schema of the OpenSAFELY-TPP database. It is part of the technical documentation of the OpenSAFELY platform to help users understand the underlying data and guide analyses.

The schema information is read from the `OpenSAFELYSchemaInformation` table, which is refreshed at the same time as the core `S1` SystemOne tables. 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, speak to TPP.

If you would like to apply to use the OpenSAFELY platform please read our documentation, the principles of the platform, and information about our pilot programme for onboarding external users.

If you want to see the Python code used to create this notebook, you can view it on GitHub.

### Data sources¶

The core SystmOne primary care datasets are held in the `S1` tables in the OpenSAFELY-TPP database. Other externally-linked data sources are listed below, with the table name given in brackets:

- All positive or negative SARS-CoV2 tests, from SGSS (`SGSS_AllTests_Positive` and `SGSS_AllTests_Negative`)
- First-ever positive or negative SARS-CoV2 test, from SGSS (`SGSS_Positive` and `SGSS_Negative`)
- A&E attendances, from SUS Emergency Care data (`EC`)
- In-patient hospital admissions, from SUS Admitted Patient Care Spells data (`APCS`)
- Out-patient hospital appointments, from SUS (`OPA`)
- Covid-related ICU admissions, from ICNARC (`ICNARC`)
- Covid-related in-hospital deaths, from CPNS (`CPNS`)
- COVID-19 Infection Survey, from ONS (`ONS_CIS`)
- All-cause registered deaths, from ONS (`ONS_Deaths`)
- High cost drugs (`HighCostDrugs`)
- Unique Property Reference Number, used for deriving household variables (`UPRN`)
- Master Patient Index (`MPI`)
- Health and Social Care Worker identification, collected at the point of vaccination (`HealthCareWorker`)

Some of these tables are accompanied by additional tables with further data. For instance, `OPA` contains the core out-patient appointment event data, and is supplemented by the `OPA_Cost`, `OPA_Diag`, `OPA_Proc` tables.

OpenSAFELY Users: If you are expecting to see a table that is not listed, this could be because the table hasn't been added to the schema yet. Please get in touch via Slack to request that this be added.

In [1]:

    ## Import libraries

    %load_ext autoreload
    %autoreload 2

    import pyodbc
    import os
    import pandas as pd
    import numpy as np
    from datetime import date, datetime
    from IPython.display import display, Markdown

    import sys
    sys.path.append('../lib/')
    from functions import *

In [2]:

    # get the server credentials from environ.txt

    dbconn = os.environ.get('FULL_DATABASE_URL', None).strip('"')

In [3]:

    ## Import schema data and date

    with closing_connection(dbconn) as cnxn:
        table_schema = pd.read_sql("""select * from OpenSAFELYSchemaInformation""", cnxn)

    today = date.today()

### Notebook run date¶

In [4]:

    display(Markdown(f"""This notebook was run on {today.strftime('%Y-%m-%d')}.  The information below reflects the state of the OpenSAFELY-TPP as at this date."""))

This notebook was run on 2022-09-27. The information below reflects the state of the OpenSAFELY-TPP as at this date.

## Table names by source¶

The table below lists all the data tables available in the OpenSAFELY-TPP database and where the data originate from.

In [5]:

    table_names = table_schema[['DataSource', 'TableName']].drop_duplicates().sort_values(['DataSource', 'TableName'])
    table_names = table_names[table_names['DataSource']!=""]
    display(table_names.reset_index(drop=True).style.set_properties(**{'text-align': 'left'}))

| DataSource | TableName
--- | --- | ---
0 | ICNARC | ICNARC
1 | NHSD_HIGH_COST_DRUGS | HighCostDrugs
2 | NHSE_APCS | APCS
3 | NHSE_APCS | APCS_Cost
4 | NHSE_APCS | APCS_Der
5 | NHSE_CPNS | CPNS
6 | NHSE_EC | EC
7 | NHSE_EC | EC_AlcoholDrugInvolvement
8 | NHSE_EC | EC_Comorbidities
9 | NHSE_EC | EC_Cost
10 | NHSE_EC | EC_Diagnosis
11 | NHSE_EC | EC_Investigation
12 | NHSE_EC | EC_PatientMentalHealth
13 | NHSE_EC | EC_Treatment
14 | NHSE_ECDS | ECDS
15 | NHSE_ECDS | ECDS_EC_Diagnoses
16 | NHSE_HEALTH_CARE_WORKER | HealthCareWorker
17 | NHSE_MPI | MPI
18 | NHSE_MPI | UPRN
19 | NHSE_OPA | OPA
20 | NHSE_OPA | OPA_Cost
21 | NHSE_OPA | OPA_Diag
22 | NHSE_OPA | OPA_Proc
23 | NHSE_SGSS | SGSS_AllTests_Negative
24 | NHSE_SGSS | SGSS_AllTests_Positive
25 | NHSE_SGSS | SGSS_Negative
26 | NHSE_SGSS | SGSS_Positive
27 | NHSE_Therapeutics | Therapeutics
28 | ONS_CIS | ONS_CIS
29 | ONS_DEATHS | ONS_Deaths
30 | OS_BUILD | BuildInfo
31 | OS_BUILD | CodeCountIndicator
32 | OS_BUILD | LatestBuildTime
33 | OS_BUILD | OpenSAFELYSchemaInformation
34 | OS_DERIVED | Household
35 | OS_DERIVED | HouseholdMember
36 | OS_DERIVED | MSOA_PopulationEstimates_2019
37 | OS_DERIVED | PotentialCareHomeAddress
38 | S1 | Appointment
39 | S1 | CTV3Dictionary
40 | S1 | CTV3Hierarchy
41 | S1 | CodedEvent
42 | S1 | CodedEventRange
43 | S1 | Consultation
44 | S1 | DataDictionary
45 | S1 | ICD10Dictionary
46 | S1 | MedicationDictionary
47 | S1 | MedicationIssue
48 | S1 | MedicationRepeat
49 | S1 | MedicationSensitivity
50 | S1 | Organisation
51 | S1 | Patient
52 | S1 | PatientAddress
53 | S1 | QOFClusterReference
54 | S1 | RegistrationHistory
55 | S1 | UnitDictionary
56 | S1 | Vaccination
57 | S1 | VaccinationReference
58 | S1 | YCodeToSnomedMapping
59 | UK_Renal_Register | UKRR

## Table Schema¶

The schema for each table contains the following info:

- `ColumnName`, the column name.
- `ColumnType`, the column type, for example integer, numeric or date — see SQL Server data types documentation for more details.
- `Precision`, `Scale` and `MaxLength` — see SQL Server precision, scale, and length documentation for more details.
- `IsNullable`, are Null values accepted.

The schema for each table is printed below.

In [6]:

    pd.set_option('display.max_columns', None)

    for source in table_names['DataSource'].unique():

        display(Markdown("\n"))
        display(Markdown(f"### {source}"))

        for table in table_names.loc[table_names['DataSource']==source, 'TableName']:
            tab = table_schema[table_schema['TableName']==table]
            tab = tab.drop(columns=['TableName', 'DataSource', 'ColumnId', 'CollationName'])
            display(Markdown(f"#### {table}"))
            display(tab.set_index('ColumnName'))

### ICNARC¶

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

### NHSD_HIGH_COST_DRUGS¶

#### HighCostDrugs¶

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

### NHSE_APCS¶

#### APCS¶

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

#### APCS_Cost¶

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

#### APCS_Der¶

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

### NHSE_CPNS¶

#### CPNS¶

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

### NHSE_EC¶

#### EC¶

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

#### EC_AlcoholDrugInvolvement¶

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

#### EC_Comorbidities¶

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

#### EC_Cost¶

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

#### EC_Diagnosis¶

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

#### EC_Investigation¶

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

#### EC_PatientMentalHealth¶

| ColumnType | MaxLength | Precision | Scale | IsNullable
--- | --- | --- | --- | --- | ---
ColumnName |  |  |  |  |
Patient_ID | bigint | 8 | 19 | 0 | False
EC_Ident | bigint | 8 | 19 | 0 | False
MH_Classification_01 | varchar | 20 | 0 | 0 | True
MH_Start_Date_01 | date | 3 | 10 | 0 | True
MH_Expiry_Date_01 | date | 3 | 10 | 0 | True
... | ... | ... | ... | ... | ...
MH_Start_Date_23 | date | 3 | 10 | 0 | True
MH_Expiry_Date_23 | date | 3 | 10 | 0 | True
MH_Classification_24 | varchar | 20 | 0 | 0 | True
MH_Start_Date_24 | date | 3 | 10 | 0 | True
MH_Expiry_Date_24 | date | 3 | 10 | 0 | True

74 rows × 5 columns

#### EC_Treatment¶

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

### NHSE_ECDS¶

#### ECDS¶

| ColumnType | MaxLength | Precision | Scale | IsNullable
--- | --- | --- | --- | --- | ---
ColumnName |  |  |  |  |
Interpreter_Language_SNOMED_CT | varchar | 18 | 0 | 0 | True
Interpreter_Language_Code_Approved | varchar | 5 | 0 | 0 | True
Overseas_Visitor_Charging_Category | varchar | 1 | 0 | 0 | True
Site_Code_of_Treatment | varchar | 5 | 0 | 0 | True
EC_Department_Type | varchar | 2 | 0 | 0 | True
... | ... | ... | ... | ... | ...
Accommodation_Status_Code_Approved | varchar | 5 | 0 | 0 | True
Preferred_Spoken_Language_SNOMED_CT | varchar | 18 | 0 | 0 | True
Preferred_Spoken_Language_Code_Approved | varchar | 5 | 0 | 0 | True
Accessible_Information_Professional_Required_SNOMED_CT | varchar | 18 | 0 | 0 | True
Accessible_Information_Professional_Required_Code_Approved | varchar | 5 | 0 | 0 | True

143 rows × 5 columns

#### ECDS_EC_Diagnoses¶

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

### NHSE_HEALTH_CARE_WORKER¶

#### HealthCareWorker¶

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

### NHSE_MPI¶

#### MPI¶

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

#### UPRN¶

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

### NHSE_OPA¶

#### OPA¶

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

#### OPA_Cost¶

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

#### OPA_Diag¶

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

#### OPA_Proc¶

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

### NHSE_SGSS¶

#### SGSS_AllTests_Negative¶

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

#### SGSS_AllTests_Positive¶

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

#### SGSS_Negative¶

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

#### SGSS_Positive¶

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

### NHSE_Therapeutics¶

#### Therapeutics¶

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

### ONS_CIS¶

| ColumnType | MaxLength | Precision | Scale | IsNullable
--- | --- | --- | --- | --- | ---
ColumnName |  |  |  |  |
Patient_ID | bigint | 8 | 19 | 0 | False
visit_id | varchar | 100 | 0 | 0 | True
dataset | int | 4 | 10 | 0 | True
visit_date | date | 3 | 10 | 0 | True
samples_taken_date | date | 3 | 10 | 0 | True
... | ... | ... | ... | ... | ...
patient_facing_clean | int | 4 | 10 | 0 | True
work_status_clean | int | 4 | 10 | 0 | True
SOC_occupation | varchar | 100 | 0 | 0 | True
geography_name | varchar | 100 | 0 | 0 | True
geography_code | varchar | 100 | 0 | 0 | True

93 rows × 5 columns

### ONS_DEATHS¶

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

### OS_BUILD¶

#### BuildInfo¶

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

#### CodeCountIndicator¶

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

#### LatestBuildTime¶

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

#### OpenSAFELYSchemaInformation¶

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

### OS_DERIVED¶

#### Household¶

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

#### HouseholdMember¶

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

#### MSOA_PopulationEstimates_2019¶

| ColumnType | MaxLength | Precision | Scale | IsNullable
--- | --- | --- | --- | --- | ---
ColumnName |  |  |  |  |
Age_1 | int | 4 | 10 | 0 | False
Age_2 | int | 4 | 10 | 0 | False
Age_3 | int | 4 | 10 | 0 | False
Age_4 | int | 4 | 10 | 0 | False
Age_5 | int | 4 | 10 | 0 | False
... | ... | ... | ... | ... | ...
MSOA_Code | varchar | 50 | 0 | 0 | False
LA_Code_2019 | varchar | 50 | 0 | 0 | False
LA_Code_2020 | varchar | 50 | 0 | 0 | False
Age_All | int | 4 | 10 | 0 | False
Age_0 | int | 4 | 10 | 0 | False

95 rows × 5 columns

#### PotentialCareHomeAddress¶

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

### S1¶

#### Appointment¶

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

#### CTV3Dictionary¶

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

#### CTV3Hierarchy¶

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

#### CodedEvent¶

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

#### CodedEventRange¶

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

#### Consultation¶

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

#### DataDictionary¶

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

#### ICD10Dictionary¶

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

#### MedicationDictionary¶

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

#### MedicationIssue¶

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

#### MedicationRepeat¶

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

#### MedicationSensitivity¶

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

#### Organisation¶

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

#### Patient¶

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

#### PatientAddress¶

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

#### QOFClusterReference¶

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

#### RegistrationHistory¶

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

#### UnitDictionary¶

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

#### Vaccination¶

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

#### VaccinationReference¶

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

#### YCodeToSnomedMapping¶

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

### UK_Renal_Register¶

#### UKRR¶

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

In [ ]: