Query Data Dictionary

This is a work in progress. Only tested with CJ queries.

Given the GUID of a query, this will spit out a rather rough “data dictionary” showing all the possible prompt outputs for each field or the source of each field for fields like Address Country.

Useful for communicating across orgs/teams when building integrations.

SELECT qp.[id]
    ,qp.[name] [Export Name]
    ,lq.[field]
    ,lq.[select]
    ,lq.[field_prompt]
    ,lq.[field_value]
    ,lp.[value]
    ,lp.[short]
    ,lp.[index]
    ,lp.[category]
    ,lp.[export]
    ,lp.[export2]
    ,lp.[export3]
    ,lp.[export4]
    ,lp.[export5]
    ,lp.[active]
FROM [query.part] qp
INNER JOIN [lookup.query] lq
    ON lq.[id] = qp.[source]
        OR lq.[id] = qp.[parent]
LEFT JOIN [lookup.prompt] lp
    ON lp.[key] = lq.[field_prompt]
WHERE 1 = 1
    AND qp.[type] = 'export'
    AND qp.[query] = '6768b9a9-1839-44af-b5a4-605b0a9ab667'
ORDER BY qp.[order]
    ,lp.[order]