List tables with most relationships in Teradata

Query below lists tables with most relationships (both foreign keys and FK references from other tables).

See also:


SELECT  TableName,
        COUNT(TableName) as Relationships,
        COUNT(ReferencedTables) as ForeignKeys,
        COUNT(ReferencingTable) as "References",
        COUNT(DISTINCT RelatedTable) as RelatedTable,
        COUNT(DISTINCT ReferencedTables) as ReferencedTables,
        COUNT(DISTINCT ReferencingTable) as ReferencingTables
    SELECT  DISTINCT ParentDB || '.' || ParentTable AS TableName,
            ChildDB || '.' || ChildTable AS RelatedTable,
            ChildDB || '.' || ChildTable as ReferencingTable,
            CAST(NULL AS VARCHAR(255)) as ReferencedTables,
    FROM    DBC.All_RI_ParentsV
    SELECT  DISTINCT ChildDB || '.' || ChildTable,
            ParentDB || '.' || ParentTable,
            CAST(NULL AS VARCHAR(255)),
            ParentDB || '.' || ParentTable,
    FROM    DBC.All_RI_ChildrenV
) Rel
GROUP BY    TableName
ORDER BY    Relationships DESC;


  • TableName - name of table preceded by database name
  • Relationships - number of relationships (FKs and FK references)
  • ForeignKeys - number of FKs in a table
  • References - number of references from other tables
  • RelatedTables - number of different related tables (if table refers to and is referenced by same table it is counted as single relation)
  • ReferencedTables - number of tables referenced by this table
  • ReferencingTables - number of tables referencing to this table with FK

Note: Multiple FK's can refers to one table, so number of foreign keys might be diffrent than number of referenced tables (same property applies to references and referencing tables)


  • One row represents one table in a database
  • Scope of rows: all tables with at least one relationship in a database
  • Ordered by number of table's relationships from the ones with the most

Sample results

There are no comments. Click here to write the first comment.