It is often handy to get all tables that reference a certain table. This is espically handy when running audit reports, determining what impacts deleting a record would have, and more. To do this we can use the following script with the option of making a recursive query to find child tables of the tables (helpful if doing an audit query, for example)
Here’s the code:
// Pass in table name and true if you want to recurse
// Recursion not recommended for things like 'sys_user' because you'll
// get a stack overflow
function getReferencesToTable(tableName, recurse){
if(recurse !== true){
recurse = false;
}
var gr = new GlideRecord('sys_dictionary');
gr.addQuery('sys_name', tableName);
gr.query();
gr.next();
var tableSysId = gr.getValue("sys_id");
gs.debug("Getting refs for " + tableSysId + " name is " + gr.getValue("name") + " column label is " + gr.getValue("column_label"));
var pointer = new GlideRecord('sys_dictionary');
pointer.addQuery('reference', tableName);
pointer.query();
var refs = [];
while(pointer.next()){
var fromTable = pointer.getValue("name");
var ref = {
from_table: fromTable,
reference_column: pointer.getValue("element"),
to_table: tableName
};
refs.push(ref);
}
if(recurse){
refs.forEach(function(r){
gs.debug("Find pointers to " + r.from_table);
var subRefs = getReferencesToTable(r.from_table);
if(subRefs.length > 0){
refs = refs.concat(subRefs);
}
});
}
return refs;
}
It returns an array of objects with the following data:
[
{
"from_table": "sys_ui_mobile_visualization",
"reference_column": "user",
"to_table": "sys_user"
},
{
"from_table": "u_contributing_causes_action_items",
"reference_column": "u_owner",
"to_table": "sys_user"
},
{
"from_table": "cmdb_ci_cloud_monitor_alarm",
"reference_column": "supported_by",
"to_table": "sys_user"
},
{
"from_table": "cmdb_ci_db_informix_catalog",
"reference_column": "u_watch_list",
"to_table": "sys_user"
}...
]