The Applications and other artifacts that use global variables are stored in CLOB format in the DB. You cannot run a SQL query to extract that information. You can list all the global variables in the Desktop client under Services.
I'd opened a support case and this is the answer they gave me:
"All of the Application definitions and job definitions are in the ESP_AMS_DF table under the DEFINITION column. Problem is, these are stored there in XML format. The customer can identify which applications contain some reference to the variable like this: SELECT * FROM ESP_AMS_DF WHERE ARTIFACTTYPE='APPLICATION' AND DEFINITION LIKE '%gvname%'; Replace "gvname" with the actual name of the global variable. Once they have the resulting rows from the above query, they can drill into the definition column values, find the variable and then identify the job tag it is embedded in to get the job name."
I've succesfully retrieved the required data querying that table, being in the IDENTIFIER and DEFINITION columns of the table. Now, the problem consists in manually inspect the DEFINITION field to locate the Jobs needed. Here is a short list of the secuence to apply:
1. Retrieve the XML data from DEFINITION column for all the "gvname" occurrences
2. Construct a SQL query capable of parse the DEFINITION column content looking for ocurrences of "gvname" and extract all related jobs names. In our case, the jobs using global variables are the ones that begins with "<app:nt_job name=" and finish with "> "
Currently I'm working in the code. As soon as I obtain some usable program, I'll share it here.
We schedule an export of all DE resources on a daily basis. It is then easy to search through the xml files to find references to variables, agents, arguments, pretty much anything. I use Agent Ransack which makes quick work of searching through the several thousand files that get exported.
It's not the most sophisticated method but it proves reliable.