Getting all the tables based on field values

[Community Alums]

Hi,

I know the name of my company ABC. It is a field value. Is there a way I can get all the tables with this field value? Kindly help.

Regards

Suman P.

[AnveshKumar M]

Hi @Community Alums ,

Yes you can write a Background Script which queries sys_dictionary table for a field type of Reference (I assume your company field is referring to core_company table) and refering the core_company table.

If you find any results, query the table to which the dictionary entry belongs to and where the field value belongs to sys_id of your company record in core_company.

If you are asking for String type, you can do the same but it will take longtime as the dictionary contains many string fields.

[Community Alums]

Hi @AnveshKumar M ,

Can you please help me with the background script?

Regards

Suman P.

[AnveshKumar M]

@Community Alums 

If you are looking for,

Reference Fields:

var dictGr = new GlideRecord('sys_dictionary');
dictGr.addQuery('internal_type', 'reference');
dictGr.addQuery('reference', 'core_company');
dictGr.addQuery('active', true);
dictGr.query();

while(dictGr._next()){
    var tableName = dictGr.getValue('name') + '';
    var fieldName = dictGr.getValue('element') + '';
    var tblGr = new GlideRecord(tableName);
    tblGr.addQuery(fieldName, 'YOUR_CPMPANY_SYS_ID'); //Change it to your company record sys_id in core_company table
    tblGr.query();

    while(tblGr._next()){
        gs.print('Table: ' + tableName + "\t Field Name: " + fieldName + "\t Record Sys_ID: " + tblGr.getUniqueValue());
    }

}

String Fields:

Note: this might run for longer duration

var dictGr = new GlideRecord('sys_dictionary');
dictGr.addQuery('internal_type', 'string');
dictGr.addQuery('active', true);
dictGr.query();

while(dictGr._next()){
    var tableName = dictGr.getValue('name') + '';
    var fieldName = dictGr.getValue('element') + '';
    var tblGr = new GlideRecord(tableName);
    tblGr.addQuery(fieldName, 'YOUR_CPMPANY_NAME'); //Change it to your company name
    tblGr.query();

    while(tblGr._next()){
        gs.print('Table: ' + tableName + "\t Field Name: " + fieldName + "\t Record Sys_ID: " + tblGr.getUniqueValue());
    }

}

[Ankur Bawiskar]

@Community Alums 

What's your business requirement here?