Service Now - Get tables that reference another table

Sunday Feb 9, 2020

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);
    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);
    var refs = [];
      var fromTable = pointer.getValue("name");
      var ref = {
      	from_table: fromTable,
        reference_column: pointer.getValue("element"),
        to_table: tableName
        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"