How to find all tables that might contain a reference to cmn_location?

[thomaskennedy]

We're trying to do some cleanup on cmn_location, mostly to remove duplicates. Given a batch of locations considered duplicates of each other, for each row, I want to identify all references to that row, so I can determine which one to keep and which to discard. I plan to iterate through a list of tables that might reference this location and for each one, use GlideRecord to identify references. The duplicates with zero references will generally be deleted.

So to start I want all tables that contain a Reference to cmn_location.

I looked in sys_dictionary for those rows where Reference is Location. And this returns about 1000 rows, as expected. However that list does not include incident, and I know that incident contains a reference to cmn_location. But it does not show up in sys_dictionary because its Reference is inherited from Task (which is of course in the list).

But I cannot search for references to Location X using task, because GlideRecord looks only at one table.

I could look up all the tables that inherit from task, and add them to my table-list. But I would have to add all their child tables also. There must be a better way.

So how do I know just where to look for references to a given Location?

[Bert_c1]

You can query task and order/group by 'sys_class_name' to see the child tables.

Seems you can use:

https://[your_instance.service-now.com/sys_dictionary_list.do?sysparm_query=GOTOreference.label%3DLo...

and then query those tables.  for 'task' and 'cmdb' you can use 'Task type' and 'Class' to see the child tables. Since cmdb is TPP and task is TPH, you'll see dictionary records for cmdb child tables, but not task child tables. for others in that list that may have child tables, you will need to check the table's hierarchy.

htts://docs.servicenow.com/bundle/tokyo-platform-administration/page/administer/platform-performance/concept/c_TaskTableFlattening.html

You will have to determine how you want to search the table list and report on those with a specific value for Location. I can imagine a script to process the table list and then search those tables for records with the specific location value.