The view REFERENCE_USAGE_VW makes this very easy. The fields are:
the scientific_name_id
the name of the column where the reference_id is used. Most of the time this will be reference_id, but there exceptions.
the table in which the reference_id is used
the primary key for the record in which the reference_id is used
name of the associated id field
the id of the associated record (element_global_id, element_subnational_id, eo_id, scientific_name_id, conservation_site_id, etc.)  
name of associated record, e.g. gname, sname, etc.
d_maintained_by_status_id of associated record, if applicable
inactive status of associated record, if applicable


*ASSOC_NAME is not always unique to the record, e.g. for EOs it's the sname, for COMM_CAL it's the gname, for ELEMENT_MANAGED_AREA it's the MA Name.


Here are some examples of how to use it:


1. If you just want to know where a particular reference is used, it's this simple; just enter the ID for the reference record you're interested in for "reference_id":

select * from reference_usage_vw where reference_id = 100077 


2. Here's a way to get a list of each reference_id, each table that it's found in, and how many occurrences there are in that table of that id: 

select reference_id, table_used, count(distinct assoc_id) 

from reference_usage_vw 

group by reference_id, table_used 

order by reference_id, table_used