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.

See also:

• Tables with most relationships
• Tables with most foreign keys

Query

select 
    distinct reftabschema concat '.' concat reftabname as table,
    count(*) as references, 
    count(distinct tabschema concat '.' concat tabname) as referencing_tables 
from syscat.references
group by reftabschema concat '.' concat reftabname
order by references desc

Columns

• 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

Rows

• 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

Sample results

List of most referenced tables in GSDB database. GOSALESDW.GO_TIME_DIM table is referenced 13 times from 11 different tables.