Query below lists tables with their number of foreign keys and number of tables they refer to.
SELECT ChildDB || '.' || ChildTable AS ForeignTable, COUNT(*) AS ForeignKeys, COUNT(DISTINCT ParentDB || '.' || ParentTable) AS ReferencedTables FROM DBC.All_RI_ChildrenV GROUP BY ForeignTable ORDER BY ForeignKeys DESC;
- ForeignTable - table name with database name
- ForeignKeys - number of foreign keys in a table
- ReferencedTables - number of referenced tables. Note that it is not the same as number of foreign keys, as multiple foreign keys may reference the same table.
- One row represents one table
- Scope of rows: tables in a database that have foreign keys (reference other tables)
- Ordered by number of foreign keys from the ones with the most