1

In StackOverflow, I can find the post about how to find all foreign key constraints in SQL Server.

I only can find the post about how to find all foreign key constraints for a table in Oralce (List of foreign keys and the tables they reference).

My question is: How can I list all foreign key constraints for all tables, instead of for a table in Oracle. My desire answer will be something like this one, but for Oracle.

P.S. I am using oralce 11g express edition. My oracle GUI management tool is Orace SQL developer.

Improve this question

4 Answers 4

Reset to default
3

I would do it like that ;

If you want it aggregated with all column on the same row

select distinct c1.owner, c1.table_name, c1.constraint_name, c2.owner, c2.table_name, listagg(c2.column_name,',') WITHIN GROUP ( ORDER BY C2.POSITION) OVER ( PARTITION BY c1.owner, c1.table_name, c1.constraint_name, c2.owner, c2.table_name ) column_list from dba_constraints c1 JOIN dba_cons_columns c2 ON c1.R_CONSTRAINT_NAME=C2.CONSTRAINT_NAME and c1.r_owner=c2.owner where C1.constraint_type = 'R'

or like that if you want one row per column

select c1.owner, c1.table_name, c1.constraint_name, c2.owner, c2.table_name, c2.column_name from dba_constraints c1 JOIN dba_cons_columns c2 ON c1.R_CONSTRAINT_NAME=C2.CONSTRAINT_NAME and c1.r_owner=c2.owner where C1.constraint_type = 'R' order by c1.owner, c1.table_name, c1.constraint_name, c2.position

or if you want referencing column details AND referenced columns details

with constraint_colum_list as ( select owner, table_name, constraint_name, listagg(column_name,',') WITHIN GROUP ( order by position ) as column_list FROM DBA_CONS_COLUMNS GROUP BY owner, table_name, constraint_name ) select distinct c1.owner, c1.table_name, c1.constraint_name, c2.column_list, c3.owner, c3.table_name, c3.constraint_name, c3.column_list from DBA_constraints c1 JOIN constraint_colum_list c2 ON c1.CONSTRAINT_NAME=C2.CONSTRAINT_NAME and c1.owner=c2.owner JOIN constraint_colum_list c3 ON C1.R_CONSTRAINT_NAME=C3.CONSTRAINT_NAME AND C1.R_OWNER=C3.owner where C1.constraint_type = 'R' -- AND c1.owner = 'YOUR_SCHEMA';
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

For you last solution, 'AND c1.owner = 'YOUR_SCHEMA';' If my oracle sql developer login name is 'TestDemo', so I need to do 'AND c1.owner = 'TestDemo';' Then I can only output the foreign key constraints that I specify/create. But SQL Server does not need to so, only Oracle needs this?
My answer is based on the fact that you asked for ALL foreign keys on ALL tables. If you are interested only in yours replace DBA_CONSTRAINTS by USER_CONSTRAINTS and DBA_CONS_COLUMNS by USER_CONS_COLUMNS and you will see only thee one that you own
Great. It works. May I know what are other results typically used for by using DBA_CONSTRAINTS? I only know those foreign key constraints specified by the user/us are useful because I can see the relationships between tables and columns, so I can know the project' business domain.
the catalog views sort of all work the same. you have a DBA_ version which contains data for all the database, ALL_ which contains objects the calling user has access to and a USER_ which contains only the objects owner by the user. but there are a lot of them DBS_OBJECTS DBA_TABLE DBA_TAB_PRIV etc. allof them are in the oracle documentation here : docs.oracle.com/cd/E11882_01/nav/catalog_views.htm
1

Don't use Table name in where condition and you will get FK constraints for all tables.

SELECT A.TABLE_NAME, A.COLUMN_NAME, A.CONSTRAINT_NAME, C.OWNER FROM ALL_CONS_COLUMNS A, ALL_CONSTRAINTS C WHERE A.CONSTRAINT_NAME = C.CONSTRAINT_NAME AND C.CONSTRAINT_TYPE = 'R' AND C.OWNER = 'MyDBLoginName';
Improve this answer

6 Comments

I think I need to add one more where condition 'AND C.OWNER = MyDBLoginName'. Otherwise, if I only use your code, it will list too many columns which C.OWNER is 'SYS', 'SYSTEM', 'HR', etc, which is not what I want.
Can you also output the referenced table and reference column?
my solution does it :)
@hemalp108 : You are missing a condition in your join clause i think. Constraint name beeing not unique cross schema, if you have 2 schema with a same constraint_name, you are going to match unrelated rows. you should add a AND A.OWNER=C.OWNER.
@LauDec Yes, your last solution is what I want. May I know how do you come to this solution? Any suggested reference document for me, so I can write the solution as you did?
|
0

This answer is based on LauDec's solution.

Since I only need all foreign key constraints specified by me/user, so I will use following SQL:

with constraint_colum_list as ( select owner, table_name, constraint_name, listagg(column_name,',') WITHIN GROUP ( order by position ) as column_list FROM USER_CONS_COLUMNS GROUP BY owner, table_name, constraint_name ) select distinct c1.owner, c1.table_name, c1.constraint_name, c2.column_list, c3.owner, c3.table_name, c3.constraint_name, c3.column_list from USER_CONSTRAINTS c1 JOIN constraint_colum_list c2 ON c1.CONSTRAINT_NAME=C2.CONSTRAINT_NAME and c1.owner=c2.owner JOIN constraint_colum_list c3 ON C1.R_CONSTRAINT_NAME=C3.CONSTRAINT_NAME AND C1.R_OWNER=C3.owner where C1.constraint_type = 'R';
Improve this answer

Comments

0

Below query gives Table Name, Column Name of both parent and child tables along with constraint names on each.

select a.table_name child_table, a.column_name child_column, b.table_name parent_table, b.column_name parent_column, a.position, a.constraint_name child_constraint, b.constraint_name parent_constraint from all_cons_columns a join all_constraints lc on a.owner = lc.owner and a.constraint_name = lc.constraint_name join all_constraints rc on lc.r_owner = rc.owner and lc.r_constraint_name = rc.constraint_name join all_cons_columns b on rc.owner = b.owner and rc.constraint_name = b.constraint_name and a.position = b.position where a.owner = '<Schema Name>' and lc.constraint_type = 'R' order by a.constraint_name, a.table_name, a.position

Here is the brief explanation of tables used in above SQL:

  • all_constraints - has constraint name, table name, constraint name of the FK parent table. Constraint_type field in this table defines the types of constraint, Foreign key constraints are defined by 'R'.
  • all_cons_columns - has list of column names for each constraint, position column in this table defines the order of the
    column in constraint.
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.