Have you ever had a need to quickly find out if a DB2 table has any dependencies, i.e. foreign key relationships to it and from it?
The following SQL query will show all parent tables (tables that the table references with its foreign keys) as well as all child tables (tables that reference the table with their foreign keys) for a chosen table (<TABSCHEMA>.<TABNAME>):
The query output looks like the following:
MYSCHEMA.MYTABLE is the table the query was ran against; the levels below 0 show the parent tables and the levels above 0 show the child tables (and their children…).
If you have table schema names longer than 20 characters, or table names longer than 40 characters, then you may want to change the formatting in the above query.
Also, the query will show only up to 10 levels of children, so you may want to adjust that to your needs as well.
I personally prefer to put this query inside a shell script and then simply execute it as:
Here’s the whole script showDependentTables.sh (Korn Shell version):
