Summary Table
Categories |
Total Count |
PII |
0 |
URL |
0 |
DNS |
0 |
EKL |
0 |
IP |
0 |
PORT |
0 |
VsID |
0 |
CF |
0 |
AI |
0 |
VPD |
0 |
PL |
0 |
Other |
0 |
File Content
?CREATE TABLE zraw._ccrapa_person
(
--source: A06, CDWWork, CCRA.pa_person
--fields
[station__no] VARCHAR (30) NOT NULL,
[paper_rowid] [bigint] NULL,
[paper_id] [varchar](8000) NULL,
[paper_name] [varchar](8000) NULL,
[paper_name2] [varchar](8000) NULL,
[paper_soundex3] [varchar](8000) NULL,
[paper_foreignphone] [varchar](8000) NULL,
[paper_dob] [datetime2](7) NULL,
[paper_ageyr] [varchar](8000) NULL,
[paper_agemth] [varchar](8000) NULL,
[paper_ageday] [varchar](8000) NULL,
[paper_sex_dr] [bigint] NULL,
[paper_deceased] [varchar](8000) NULL,
[paper_foreigncity] [varchar](8000) NULL,
[paper_foreignpostcode] [varchar](8000) NULL,
[paper_marital_dr] [bigint] NULL,
[paper_education_dr] [bigint] NULL,
[paper_nation_dr] [bigint] NULL,
[paper_religion_dr] [bigint] NULL,
[paper_langprim_dr] [bigint] NULL,
[paper_foreignaddress] [varchar](8000) NULL,
[paper_soundex2] [varchar](8000) NULL,
[paper_stname] [varchar](8000) NULL,
[paper_instype_dr] [bigint] NULL,
[paper_soundex1] [varchar](8000) NULL,
[paper_citycode_dr] [bigint] NULL,
[paper_statecode_dr] [bigint] NULL,
[paper_zip_dr] [bigint] NULL,
[paper_telo] [varchar](8000) NULL,
[paper_socialstatus_dr] [bigint] NULL,
[paper_telh] [varchar](8000) NULL,
[paper_foreigncountry] [varchar](8000) NULL,
[paper_country_dr] [bigint] NULL,
[paper_inscardowner] [varchar](8000) NULL,
[paper_occupation_dr] [bigint] NULL,
[paper_familydoctor_dr] [bigint] NULL,
[paper_remark] [varchar](8000) NULL,
[paper_deceased_date] [datetime2](7) NULL,
[paper_foreignid] [varchar](8000) NULL,
[paper_country_birth_dr] [bigint] NULL,
[paper_estageyear] [numeric](28, 6) NULL,
[paper_estagemonth] [numeric](28, 6) NULL,
[paper_estagetmstmp] [datetime2](7) NULL,
[paper_deceasedtime] [time](7) NULL,
[paper_regionbirth_dr] [bigint] NULL,
[paper_debtor_dr] [bigint] NULL,
[paper_age] [varchar](8000) NULL,
[paper_insurancecardholder] [varchar](8000) NULL,
[paper_surname] [varchar](8000) NULL,
[paper_stnameline1] [varchar](8000) NULL,
[paper_province_birth_dr] [bigint] NULL,
[paper_empltype_dr] [bigint] NULL,
[paper_jobtitle] [varchar](8000) NULL,
[paper_emplrelatedto] [varchar](8000) NULL,
[paper_ctrlt_dr] [bigint] NULL,
[paper_employeeno] [varchar](8000) NULL,
[paper_empldep_dr] [bigint] NULL,
[paper_discdatefrom] [datetime2](7) NULL,
[paper_discdateto] [datetime2](7) NULL,
[paper_disctype_dr] [bigint] NULL,
[paper_outstandamt] [numeric](28, 6) NULL,
[paper_employeefunction] [bigint] NULL,
[paper_employeecompany] [bigint] NULL,
[paper_employeecocontract] [varchar](8000) NULL,
[paper_guardian1_dr] [bigint] NULL,
[paper_guardian2_dr] [bigint] NULL,
[paper_outstandingdate] [datetime2](7) NULL,
[paper_expectedpaydate] [datetime2](7) NULL,
[paper_billcode] [varchar](8000) NULL,
[paper_nokname] [varchar](8000) NULL,
[paper_nokphone] [varchar](8000) NULL,
[paper_noktext] [varchar](8000) NULL,
[paper_nokctrlt_dr] [bigint] NULL,
[paper_ct_region_dr] [bigint] NULL,
[paper_ct_province_dr] [bigint] NULL,
[paper_ct_hca_dr] [bigint] NULL,
[paper_citybirth_dr] [bigint] NULL,
[paper_name3] [varchar](8000) NULL,
[paper_governcardno] [varchar](8000) NULL,
[paper_fromthisarea] [varchar](8000) NULL,
[paper_foreignnotes] [varchar](8000) NULL,
[paper_reasonforchangedata] [varchar](8000) NULL,
[paper_userupdate] [bigint] NULL,
[paper_exemptionnumber] [varchar](8000) NULL,
[paper_cityarea_dr] [bigint] NULL,
[paper_complement] [varchar](8000) NULL,
[paper_responsibleforpayment] [bigint] NULL,
[paper_mother_dr] [bigint] NULL,
[paper_father_dr] [bigint] NULL,
[paper_familygroup_dr] [bigint] NULL,
[paper_discretoutstype_dr] [bigint] NULL,
[paper_hcp_dr] [bigint] NULL,
[paper_secondphone] [varchar](8000) NULL,
[paper_nokaddress1] [varchar](8000) NULL,
[paper_nokcity_dr] [bigint] NULL,
[paper_nokzip_dr] [bigint] NULL,
[paper_nokaddress2] [varchar](8000) NULL,
[paper_email] [varchar](8000) NULL,
[paper_title_dr] [bigint] NULL,
[paper_mobphone] [varchar](8000) NULL,
[paper_indigstat_dr] [bigint] NULL,
[paper_auxinstype_dr] [bigint] NULL,
[paper_preflanguage_dr] [bigint] NULL,
[paper_pattype_dr] [bigint] NULL,
[paper_updatedate] [datetime2](7) NULL,
[paper_updatetime] [time](7) NULL,
[paper_glflag] [varchar](8000) NULL,
[paper_familydoctorclinic_dr] [varchar](8000) NULL,
[paper_donatedorgansondeath] [varchar](8000) NULL,
[paper_whonotified] [varchar](8000) NULL,
[paper_locationofdeath] [varchar](8000) NULL,
[paper_estdob] [varchar](8000) NULL,
[paper_stayingpermanently] [varchar](8000) NULL,
[paper_dateofentry] [datetime2](7) NULL,
[paper_countrycamefrom_dr] [bigint] NULL,
[paper_nationalresident] [varchar](8000) NULL,
[paper_osvstatus_dr] [bigint] NULL,
[paper_interviewedby] [varchar](8000) NULL,
[paper_dateofinterview] [datetime2](7) NULL,
[paper_dateadded] [datetime2](7) NULL,
[paper_useradded_dr] [bigint] NULL,
[paper_papmi_dr] [bigint] NULL,
[paper_nokbusphone] [varchar](8000) NULL,
[paper_lastupdatehospital_dr] [bigint] NULL,
[paper_doctordeclared_dr] [varchar](8000) NULL,
[paper_nokinformed] [varchar](8000) NULL,
[paper_procuratorfiscalinformed] [varchar](8000) NULL,
[paper_interpreterrequired] [varchar](8000) NULL,
[paper_prevhacceligible] [varchar](8000) NULL,
[paper_usualround_dr] [bigint] NULL,
[paper_employmentstat_dr] [bigint] NULL,
[paper_sourceofincome_dr] [bigint] NULL,
[paper_livingarrangement_dr] [bigint] NULL,
[paper_residencestatus_dr] [bigint] NULL,
[paper_transportused_dr] [bigint] NULL,
[paper_cancerreg] [varchar](8000) NULL,
[paper_bloodtype_dr] [bigint] NULL,
[paper_effdatecurraddress] [datetime2](7) NULL,
[paper_feedbackconsent] [varchar](8000) NULL,
[paper_aussouthseaislander_dr] [bigint] NULL,
[paper_occupation] [varchar](8000) NULL,
[paper_incompleteregistration] [varchar](8000) NULL,
[paper_familylinktext] [varchar](8000) NULL,
[paper_dvaexpirydate] [datetime2](7) NULL,
[paper_pensioncardexpirydate] [datetime2](7) NULL,
[paper_reversedby] [varchar](8000) NULL,
[paper_hospital_dr] [bigint] NULL,
[paper_name4] [varchar](8000) NULL,
[paper_name5] [varchar](8000) NULL,
[paper_name6] [varchar](8000) NULL,
[paper_name7] [varchar](8000) NULL,
[paper_name8] [varchar](8000) NULL,
[paper_dentaldisplay] [varchar](8000) NULL,
[paper_feedbackconsentdate] [datetime2](7) NULL,
[paper_address2] [varchar](8000) NULL,
[paper_passportnumber] [varchar](8000) NULL,
[paper_dependchildren_dr] [bigint] NULL,
[paper_preferredcontactmethod] [varchar](8000) NULL,
[paper_contactnotes] [varchar](8000) NULL,
[paper_accomsetting_dr] [bigint] NULL,
[paper_photo] [varchar](8000) NULL,
[paper_pdsserialnumber] [varchar](8000) NULL,
[paper_employeedatefrom] [datetime2](7) NULL,
[paper_employeedateto] [datetime2](7) NULL,
[paper_employeegrade] [varchar](8000) NULL,
[paper_house_building_no] [varchar](8000) NULL,
[paper_appartmentno] [varchar](8000) NULL,
[paper_gravida] [numeric](28, 6) NULL,
[paper_para] [numeric](28, 6) NULL,
[paper_miscarriage] [numeric](28, 6) NULL,
[paper_top] [numeric](28, 6) NULL,
[paper_preterm] [numeric](28, 6) NULL,
[paper_multbirth] [numeric](28, 6) NULL,
[paper_livingchild] [numeric](28, 6) NULL,
[paper_specneedstat_dr] [bigint] NULL,
[paper_snslastreviewdate] [datetime2](7) NULL,
[paper_snsnextreviewdate] [datetime2](7) NULL,
[paper_snslastliveupddate] [datetime2](7) NULL,
[paper_postcode] [varchar](8000) NULL,
[paper_iddoctype_dr] [bigint] NULL,
[paper_docnumber] [varchar](8000) NULL,
[paper_chinesename] [varchar](8000) NULL,
[paper_nochinname] [varchar](8000) NULL,
[paper_ccc1] [varchar](8000) NULL,
[paper_ccc2] [varchar](8000) NULL,
[paper_ccc3] [varchar](8000) NULL,
[paper_ccc4] [varchar](8000) NULL,
[paper_ccc5] [varchar](8000) NULL,
[paper_ccc6] [varchar](8000) NULL,
[paper_chineseaddress] [varchar](8000) NULL,
[paper_chinesedistrict] [varchar](8000) NULL,
[paper_chinesearea] [varchar](8000) NULL,
[paper_pager] [varchar](8000) NULL,
[paper_fax] [varchar](8000) NULL,
[paper_photodocument] [bigint] NULL,
[paper_email2] [varchar](8000) NULL,
[paper_latetop] [numeric](28, 6) NULL,
[paper_stillbirth] [numeric](28, 6) NULL,
[paper_paradisplayuk] [numeric](28, 6) NULL,
[paper_reasonforattention] [varchar](8000) NULL,
[paper_deathnotificationstatus] [varchar](8000) NULL,
[paper_externaloids] [varchar](8000) NULL,
[paper_famdocpractice] [varchar](8000) NULL,
[paper_estimatedobinfuture] [varchar](8000) NULL,
[paper_usercreatephoto] [bigint] NULL,
[paper_datecreatephoto] [datetime2](7) NULL,
[paper_timecreatephoto] [time](7) NULL,
[paper_userupdatephoto] [bigint] NULL,
[paper_dateupdatephoto] [datetime2](7) NULL,
[paper_timeupdatephoto] [time](7) NULL,
[paper_residentnumber] [varchar](8000) NULL,
[paper_primaryhouseholdmember] [varchar](8000) NULL,
[paper_appointmentsms] [varchar](8000) NULL,
[paper_freetext1] [varchar](8000) NULL,
[paper_freetext2] [varchar](8000) NULL,
[paper_freetext3] [varchar](8000) NULL,
[paper_freetext4] [varchar](8000) NULL,
[paper_freetext5] [varchar](8000) NULL,
[paper_doctypeplaceissue1] [varchar](8000) NULL,
[paper_doctypeplaceissue2] [varchar](8000) NULL,
[paper_passportplaceissue] [varchar](8000) NULL,
[paper_liveswith] [varchar](8000) NULL,
[paper_dependuponpat_dr] [bigint] NULL,
[paper_dependuponpattext] [varchar](8000) NULL,
[paper_accesstoserv_dr] [bigint] NULL,
[paper_accesstoservtext] [varchar](8000) NULL,
[paper_dependentlocality] [varchar](8000) NULL,
[paper_patientdetconfirmed] [varchar](8000) NULL,
[paper_mothername] [varchar](8000) NULL,
[paper_mothersurname] [varchar](8000) NULL,
[paper_fathername] [varchar](8000) NULL,
[paper_fathersurname] [varchar](8000) NULL,
[paper_careravailability_dr] [bigint] NULL,
[paper_prefgenderinterpreter_dr] [bigint] NULL,
[paper_canleavemessageson] [varchar](8000) NULL,
[paper_communicationneeds_dr] [bigint] NULL,
[paper_concessioncardstatus] [varchar](8000) NULL,
[paper_sendwrittencomm1stcont] [varchar](8000) NULL,
[paper_writtencommformat_dr] [bigint] NULL,
[paper_prefcareprovgender_dr] [bigint] NULL,
[paper_appointtransport_dr] [bigint] NULL,
[paper_fhcsegment_dr] [bigint] NULL,
[paper_fhcarea_dr] [bigint] NULL,
[paper_fhcmicroarea_dr] [bigint] NULL,
[paper_lbcspecies_dr] [bigint] NULL,
[paper_dateregtonhs] [datetime2](7) NULL,
[paper_datetermfromnhs] [datetime2](7) NULL,
[paper_concesscardtype_dr] [bigint] NULL,
[paper_gestationweeks] [numeric](28, 6) NULL,
[paper_gestationdays] [numeric](28, 6) NULL,
[paper_idvaliddate] [datetime2](7) NULL,
[paper_idstatus_dr] [bigint] NULL,
[paper_birthgender_dr] [bigint] NULL,
[paper_bloodgroup_dr] [bigint] NULL,
[paper_bloodgroupstatus] [varchar](8000) NULL,
[paper_bgupdatedate] [datetime2](7) NULL,
[paper_bgupdatetime] [time](7) NULL,
[paper_bgupdateuser_dr] [bigint] NULL,
[paper_bgoverwritereason_dr] [bigint] NULL,
[paper_idvaliddatefrom] [datetime2](7) NULL,
[paper_idvaliddateto] [datetime2](7) NULL,
[paper_bgtestsetitem_dr] [varchar](8000) NULL,
[paper_deceasedoutsidehosp] [varchar](8000) NULL,
[paper_nationalidcreationdate] [datetime2](7) NULL,
[paper_mentalhealtharea_dr] [bigint] NULL,
[paper_birthordernumber] [bigint] NULL,
[paper_ext_ehrconsent] [varchar](8000) NULL,
[thread__id] NUMERIC (18,0) NOT NULL,
--BatchLogID should always be the last of the authoritative source fields listed
--in the data gathered by extractors, it should be the first of the EPRS-specific fields
BatchLogID BIGINT,
--EPRS table specific: Any fields beyond this point are EPRS specific fields/FKs
--primary key
--unique key (if any)
)
ON StagingData
--when using clustered columnstore index, can't set data compression to PAGE
WITH (DATA_COMPRESSION = PAGE) --less than 10,000 rows, don't need compression
--commenting compression in. table has over 22K records
GO
--foreign keys
--indexes
--constraints
--Metadata descriptions for each field