How to get all table and view connection in oracle-CodePudding

How to extract all views and tables from a Oracle database including the column connections? I think in MySQL I could use KEY_COLUMN_USAGE which contains this information, is there anything equivalent in Oracle including the views?

Example:

Table1 |      Table2 |        Table3
ID1    |>>>>> ID1    |>>>>>>  ID2
Name   |      Date   |         Code
Adress |             |

I would like to get the information that:

Table1 is connected with Table2 via ID1>ID1 
Table2 is connected with Table3 via ID1>ID2

CodePudding user response：

What would "column connections" be?

I guess you need USER_TABLES, USER_VIEWS and USER_TAB_COLUMNS. Former 2 views returns information about tables and views, while the latter contains info about columns in those tables/views.

You'd join them as

select ...
from user_tables t join user_tab_columns on c.table_name = t.table_name

The same goes for USER_VIEWS (as USER_TAB_COLUMNS contains columns from both tables and views).

If you want to check all tables and views you have access to, you'd use ALL_TABLES, ALL_VIEWS and ALL_TAB_COLUMNS, but this time - in join condition - include the OWNER column, e.g.

select ... 
from all_tables t join all_tab_columns c on c.owner = t.owner 
                                        and c.table_name = t.table_name

Finally, if you have DBA access, then use DBA_ views (query would look the same as for ALL_ views).

CodePudding user response：

Consider this when saying you want to expose table and view "connections", which is traditionally referred to as relationships.

create table EMP (ID number primary key);

create or replace view FOOLS_ERRAND as
select ID EMP_ID from EMP;

Do you really want to try to write code that can say FOOLS_ERRAND is related to EMP? If you do, then be prepared to execute SQL statement parsing code that can create and scan a parse tree for the entire select statement syntax. Even if you obtain an execution plan for select * from FOOLS_ERRAND, there's no guarantee that appearances of EMP or its unique index scan in the row source operations guarantee the view is related to the table (like a table is related to another). About the best you could say is that the view makes reference to the table.

I recommend you stick with retrieving a table's declared constraints either from ALL_CONSTRAINTS and ALL_CONS_COLUMNS or its USER and DBA variants.