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

Query below lists all tables refrenced with foregin key by specific table.

Query

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

Columns

  • ForeignTable - foreign table name with database name - the table you provided as a parameter
  • Rel - relationship symbol implicating FK and direction
  • PrimaryTable - primary (rerefenced) tables names with database name - the tables you are looking for

Rows

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

Notes

  • There can be more tables with the same name. If that's the case, uncomment where clause and provide database name

Sample results

All tables referenced with FK by Sales.dept_emp table:

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