Query below lists tables that are most referenced by other tables with foreign keys. Those are the dictionary tables such as country, product or person. In data warehouses those are dimension tables.
distinct reftabschema concat '.' concat reftabname as table,
count(*) as references,
count(distinct tabschema concat '.' concat tabname) as referencing_tables
group by reftabschema concat '.' concat reftabname
order by references desc
- table - name of the table with schema name
- references - number of foreign keys referencing to this table
- referencing_tables - number of different tables referencing to this table
- One row represents one table in a database
- Scope of rows: tables being used in foreign keys as primary key table
- Ordered by number of foregin keys descending
List of most referenced tables in GSDB database. GOSALESDW.GO_TIME_DIM table is referenced 13 times from 11 different tables.