// JWJ0215 2022
//This script searches all tables for the record identified by a given sys_id.
//It returns the table name where the sys_id was found, and links to the List View and Form View where the record may be found.

//Specify the sys_id you are searching for.
var sysIdToFind = 'b08ad363537411106883ddeeff7b12db';

//Invoke the search function.
FindRecGivenSysId(sysIdToFind);

function FindRecGivenSysId(sys_id) {
    //Exclude any tables which are causing a problem for our search.
    var tablesToExcludeFromSearch = [
        "itom_licensing_exclusion_metadata",
        "sn_employee_app_access",
        "sn_employee_profile",
        "sn_m2m_note_template_for_table",
        "sys_ux_sitemap_definition",
    ];
    var baseTablesToSearch = new GlideRecord('sys_db_object');
    var instanceURI = gs.getProperty('glide.servlet.uri');
    var recordFound = false;
    var current;
    var dict;

    //OutputToAll('Searching base tables in sys_db_object for ' + sys_id);

    //Grab base tables, excluding text-indexing, virtual, and sysx_ tables.
    //Important to know: Records from Child tables will still be found in a search of the base table!  There is no need to search child tables directly.
    //The function getRecordClassName() can then be used to tell you the specific (child) table the record was found in.
    baseTablesToSearch.addNullQuery("super_class");
    baseTablesToSearch.addQuery("name", "NOT LIKE", "ts_c_");
    baseTablesToSearch.addQuery("name", "NOT LIKE", "v_");
    baseTablesToSearch.addQuery("name", "NOT IN", tablesToExcludeFromSearch.toString());
    baseTablesToSearch.query();

    while (baseTablesToSearch.next()) {
        //OutputToAll('Searching: ' + baseTablesToSearch.name);

        current = new GlideRecord(baseTablesToSearch.name);

        //Find out if there is even a "sys_id" column in the table.  If there is not, just skip it.
        dict = new GlideRecord('sys_dictionary');
        dict.addQuery('name', baseTablesToSearch.name);
        dict.addQuery('element', 'sys_id');
        dict.queryNoDomain();
        if (!dict.next()) continue;

        //Now search for the actual sys_id in the current table.
        current.addQuery('sys_id', sys_id);
        //Prevent Query Rules from running, if allowed, as these may limit our results.
        current.setWorkflow(false);
        current.queryNoDomain();

        if (current._next()) {
            //We found the actual record by its sys_id value!
            recordFound = true;
            //OutputToAll('Record found in base table: ' + baseTablesToSearch.name);
            //OutputToAll('Record found in child table: ' + current.getClassDisplayValue() + " " + current.getRecordClassName());

            var listViewURL = instanceURI + "/nav_to.do?uri=/[theTable]_list.do?sysparm_query=sys_id=[theSysId]";
            listViewURL = listViewURL.replace("[theTable]", current.getRecordClassName());
            listViewURL = listViewURL.replace("[theSysId]", sys_id);
            var listViewHTML = '<a href="' + listViewURL + '"target=_blank>' + "List View" + '</a>';

            var formViewURL = instanceURI + "/nav_to.do?uri=/[theTable].do?sys_id=[theSysId]";
            formViewURL = formViewURL.replace("[theTable]", current.getRecordClassName());
            formViewURL = formViewURL.replace("[theSysId]", sys_id);
            var directLinkHTML = '<a href="' + formViewURL + '"target=_blank>' + "Form View" + '</a>';

            OutputToAll("TABLE: " + current.getClassDisplayValue() + " ( " + current.getRecordClassName() + " )");
            OutputToAll("SYS_ID: " + sys_id);
            OutputToForm(listViewHTML);
            OutputToForm(directLinkHTML);
            OutputToLog("List View: " + listViewURL);
            OutputToLog("Form View: " + formViewURL);

            //We found what we came for.  No need to keep searching.
            break;
        }
    }

    if (!recordFound) {
        OutputToAll("sys_id " + sys_id + " could not be found in any of the tables searched.");
    }

    //OutputToAll('FindRecGivenSysId completed successfully');
}

function OutputToAll(outputString) {
    OutputToForm(outputString);
    OutputToLog(outputString);
}

function OutputToForm(outputString) {
    gs.addInfoMessage(outputString);
}

function OutputToLog(outputString) {
    //Log Prefix makes it much easier to find the statements we care about in the system log (table syslog).
    var logPrefix = "FindRecGivenSysId: ";
    gs.print(logPrefix + outputString);
    gs.log(logPrefix + outputString);
}