Find Table Given Sys_Id

[G24]

What are some excellent ways to search through ALL (or most) ServiceNow Tables for a know sys_id value?

Such as this one: ae706e530a96001a00120cb31709c1d6

[sushantmalsure]

@G24 : can you elaborate more on the requirement?

Do you want to know table's sys_id or sys_id's of records from all servicenow tables?

[G24 · Accepted solution]

Here is a script I've written for this, based on this discussion.

//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 = 'ae706e530a96001a00120cb31709c1d6';

//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);
}

This version is intended to be easy to understand and to modify.  It can be run from "Scripts - Background", but I like to run it from table "sysauto_script" using a script of type "On Demand".   That allows you to more easily Save and Edit the script, and it gives you line numbers, etc.

That method will look like this:

Using the excellent Debug Now button, the script results can be seen directly on the form, which looks like this:

Or if you want to use the regular "Execute Now" UI Action that comes out of the box in table sysauto_script, then you can view your results in the System Log (table sys_log), and that will look as follows:

Let me know if you found this helpful, or if not, what is an easier way.  Thanks.

[G24]

Here is the Script itself:

[Tushar]

Hi there,

something like this ?

function searchSysIDAcrossAllTables(sysID) {
  var allTables = new GlideRecord('sys_db_object');
  allTables.query();
  
  while (allTables.next()) {
    var tableName = allTables.getValue('name');
    var gr = new GlideRecord(tableName);
    if (!gr.isValid()) {
      // Skip invalid tables (e.g., sys_db_object itself)
      continue;
    }
    
    gr.addQuery('sys_id', sysID);
    gr.query();
    
    if (gr.next()) {
      gs.info('Found sys_id ' + sysID + ' in table ' + tableName);
  
    }
  }
}

// Call the function with the known sys_id value you want to search
searchSysIDAcrossAllTables('ae706e530a96001a00120cb31709c1d6');

Mark as correct and helpful if it solved your query.

Regards,
Tushar