Dependent Tables in DB2
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):
I don’t think it’s your SQL. I know the DBA who wrote this SQL. Did you check if he is happy to have to use it?
Hi ‘P’
If you would be so kind as to provide me with a contact to the DBA you claim wrote this SQL, I will be more than happy to get in touch with him and compare our notes.
If it turns out I did use his SQL (or parts of it), which I honestly don’t remember, I will be more than happy (once again) to acknowledge this publicly in this blog!
Damir Wilder
damir.wilder@triton.co.uk