List tables with most foreign keys in Teradata database

Query below lists tables with their number of foreign keys and number of tables they refer to.

See also:

Query

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;

Columns

  • 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.

Rows

  • 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

Sample results

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