List all tables refrencing specific table (by FK) in Teradata database

Query below lists tables which are refering with foregin key to specific table provided in query.

Query

SELECT DISTINCT ChildDB || '.' || ChildTable AS ForeignTable,
        '>-' AS Rel,
        ParentDB || '.' || ParentTable AS PrimaryTable
FROM    DBC.All_RI_ChildrenV
WHERE   ParentTable = 'employees'
--AND ParentDB = 'database_name'
ORDER BY    ForeignTable;

Columns

  • ForeignTable - referencing table name with database name - table we look for
  • Rel - relationship symbol implicating FK and direction
  • PrimaryTable - referenced table name preceded by database name - table provided in condition statement

Rows

  • One row represents referenced table
  • Scope of rows: tables referencing to table with provided name (and optionally database)
  • Ordered by database name and name of referencing table

Sample results

Tables referencing to Sales.employees table with FK.

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