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

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.