CODEWITHSUNDEEP
db2ibm-midrangedb2-400
user7392562
user7392562

Reputation:

DB2 find tables that reference my table

[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?

Upvotes: 3

Views: 4327

Answers (2)

jmarkmurphy
jmarkmurphy

Reputation: 11473

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

Upvotes: 5

data_henrik
data_henrik

Reputation: 17118

DB2 on IBM i (AS 400) offers a list of all system tables, the system catalog. It is the place where metadata is stored. One of the views, SYSCST, is the view with all constraints, the view SYSCSTCOL has information about the constraint columns, and SYSCSTDEP stores the dependencies.

So you would query SYSCST, SYSCSTCOL and SYSCSTDEP for finding the details.

Upvotes: 0

Related Questions