[ Knowledge Sharing ] SQL query snippet to generate a data-dictionary of csv/xls of all Event/tracker programs available in the system in the sorting order

Just incase if somebody is interested to create a data dictionary of csv/xls for all the programs in the system. Please note test it before you use it officially, yet to confirm it works for all scenarios, neverthless it’s a good starting snippet

select programstage.name as program_name, programstagesection.name as section_name, 
programstagesection.sortorder as section_order, programstagesection_dataelements.sort_order, COALESCE(dataelement.formname,dataelement.name,dataelement.formname) as form_name, dataelement.uid as uid, dataelement.valuetype
from programstage, programstagedataelement , dataelement , 
programstagesection,programstagesection_dataelements
where programstage.programstageid=programstagedataelement.programstageid
and programstagedataelement.dataelementid = dataelement.dataelementid
and programstagesection.programstageid=programstage.programstageid
and programstagesection_dataelements.programstagesectionid=programstagesection.programstagesectionid
and programstagesection_dataelements.dataelementid=dataelement.dataelementid
order by programstage.name,programstagesection.sortorder,programstagesection.name,programstagesection_dataelements.sort_order

3 Likes

Thank you @jthomas for the knowledge sharing post! :love_letter:

1 Like