CODEWITHSUNDEEP
oracle-databaseplsqlsql-navigator
Anastasiia Mishchenko
Anastasiia Mishchenko

Reputation: 11

How can I find all tables with no reference on it

For example, a table A references table B, but there no such tables, that reference table A. I need to use system tables(such as user constraints) to solve this problem

Upvotes: 1

Views: 78

Answers (1)

Littlefoot
Littlefoot

Reputation: 142778

For example:

SELECT u.table_name
  FROM user_tables u
 WHERE u.table_name NOT IN (SELECT c.table_name
                              FROM user_constraints rc
                                   JOIN user_constraints c
                                      ON c.constraint_name =
                                            rc.r_constraint_name
                             WHERE rc.constraint_type = 'R');

Or, if you prefere (NOT) EXISTS:

SELECT u.table_name
  FROM user_tables u
 WHERE NOT EXISTS
          (SELECT NULL
             FROM user_constraints rc
                  JOIN user_constraints c
                     ON c.constraint_name = rc.r_constraint_name
            WHERE     rc.constraint_type = 'R'
                  AND u.table_name = c.table_name);

Upvotes: 1

Related Questions