Mar
26
2019 Posted by Damir Wilder

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>):

"Dependent Tables DB2

The query output looks like the following:

"Dependent Tables DB2 Damir Wilder

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:

"/DNAME

Here’s the whole script showDependentTables.sh (Korn Shell version):

"

2 thoughts on “Dependent Tables in DB2”

  1. P says:

    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?

  2. Damir Wilder says:

    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

Leave a Reply

Your email address will not be published. Required fields are marked *

« | »
Have a Question?

Get in touch with our expert team and see how we can help with your IT project, call us on +44(0) 870 2411 550 or use our contact form…