DB2 find tables that reference my table

Question

[db2-as400] I have a table ENR_DATA that has column EnrollmentID as a primary key. This column is referred by many tables as a "foreign key". Is there a way to list down all those tables who refer to EnrollmentID of ENR_DATA table?

Answer 1

There are a few catalog views that each give just a part of the answer, and you have to join them all together.

• SYSCST provides a list of constraints with the constrain type. From here we can select out Foreign Key constraints. TABLE_NAME in this table is the table that contains the foreign key.
• SYSKEYCST provides a list of columns for a given Foreign Key, Primary Key, or Unique constraint along with the ordinal position of the column in the key, and the associated table name.
• SYSREFCST provides the name of the Primary or Unique Key constraint that is referenced by a given Foreign Key Constraint.

From these three tables we can write the following SQL:

select cst.constraint_schema, cst.constraint_name,
       fk.table_schema, fk.table_name, fk.ordinal_position, fk.column_name, 
       pk.table_schema, pk.table_name, pk.column_name 
  from qsys2.syscst cst
    join qsys2.syskeycst fk 
      on fk.constraint_schema = cst.constraint_schema 
        and fk.constraint_name = cst.constraint_name
    join qsys2.sysrefcst ref 
      on ref.constraint_schema = cst.constraint_schema 
        and ref.constraint_name = cst.constraint_name
    join qsys2.syskeycst pk 
      on pk.constraint_schema = ref.unique_constraint_schema 
        and pk.constraint_name = ref.unique_constraint_name
  where cst.constraint_type = 'FOREIGN KEY' 
    and fk.ordinal_position = pk.ordinal_position
    and pk.table_name = 'ENR_DATA'
    and pk.column_name = 'ENROLLMENTID'
  order by cst.constraint_schema, cst.constraint_name;

This will get you the table names that reference 'ENR_DATA' via foreign key. Note I have ENROLLMENTID in all upper case. That is how DB2 for i stores all column names unless they are quoted using "".