0

Using: Oracle SQL Developer

Hi I try to explain my Problem with a simple example: enter image description here

I need a Oracle SQL Query that returns me all Foreign Key related columns from all child tables ( Table Employee and Table Building) that reference the parent table Table Organizational unit

The result for my example would look like this:

SELECT All COLUMNS OF All Tables THAT have an entry referenced as foreign key for table Table Organizational unit WHERE Table Organizational unit.Code = 'HR'

--Result

Table Building
ID , ORG_UNIT_REF,  Building Type
152, 2           ,   Main Building

Table Employee
ID,  ORG_UNIT_REF,  Employee Name
13,  2           ,  Max Doe

This means all Tables with their table name, column names and matching column contents have to be printed out.

I already found all the Referening tables and Constraint names by using this answer

SELECT a.table_name, a.column_name, a.constraint_name, c.owner, 
       -- referenced pk
       c.r_owner, c_pk.table_name r_table_name, c_pk.constraint_name r_pk
  FROM all_cons_columns a
  JOIN all_constraints c ON a.owner = c.owner
                        AND a.constraint_name = c.constraint_name
  JOIN all_constraints c_pk ON c.r_owner = c_pk.owner
                           AND c.r_constraint_name = c_pk.constraint_name
 WHERE c.constraint_type = 'R'
   AND a.table_name = :TableName

For my example the above query gives me something like that:

"TABLE_NAME","CONSTRAINT_NAME","STATUS","OWNER"
"Table Employee","FK_CONSTRAINT","ENABLED","TESTSCHEMA"
"Table Building","FK_CONST","ENABLED","TESTSCHEMA"

Now I know all the child tables that reference my parent table Organizational unit. Now I also want a query that fetches me all the rows in all the tables where the foreign key matches.

Viktor Carlson
  • 989
  • 1
  • 13
  • 36
  • 1
    Do you mean that you don't actually know in advance which tables have foreign keys referencing the organisational table? Which means that you want some code that will find all of those tables, and then ***also*** query those tables for rows that relate to a target row in the organisational table? – MatBailie Mar 28 '19 at 12:48
  • 2
    You need to use Dynamic SQL. There are plenty of answers on StackOverflow showing how to generate and execute queries using table and column names from the data dictionary. [For instance this one](https://stackoverflow.com/a/3288994/146325). Use the query you already have to drive a CURSOR FOR loop which assembles and executes the query. You will need to populate collections, which may be problematic if your result sets are large. If you cannot write the code you need from this hint, please provide more information regarding data volumes, also how you will use the output. – APC Mar 28 '19 at 12:55
  • The answer depends on exactly what format you want the results in. If the tables have different columns then the results can't be purely relational. For example, someone asked a similar question a few days ago, requesting XML format. See my answer [here](https://stackoverflow.com/a/55295360/409172) for some sample code. – Jon Heller Mar 29 '19 at 03:10

0 Answers0