CODEWITHSUNDEEP
oracle-databaseconstraintsddlschemaexportdbms-metadata
Ronaldus
Ronaldus

Reputation: 29

Which Oracle view contains all constraints together?

I'm trying to get CONSTRAINTS from user_objects table like this:

 select CASE object_type
      WHEN 'DATABASE LINK' then 'dblinks'
      WHEN 'FUNCTION' then 'functions'
      WHEN 'INDEX' then 'indexes'
      WHEN 'PACKAGE' then 'packages'
      WHEN 'PROCEDURE' then 'procedures'
      WHEN 'SEQUENCE' then 'sequences'
      WHEN 'TABLE' then 'tables'
      WHEN 'TRIGGER' then 'triggers'
      WHEN 'VIEW' then 'views'
      WHEN 'SYNONYM' then 'synonyms'
      WHEN 'GRANT' then 'grants'
      WHEN 'CONSTRAINT' then 'constraints'
      ELSE object_type
      END||'|'||
      CASE object_type
      WHEN 'DATABASE LINK' then 'DB_LINK'
      ELSE object_type
      END||'|'||object_name
from user_objects
where object_name not like 'BIN$%'
and object_type not like '%PARTITION'
and object_type not in ('PACKAGE BODY')
order by object_type
; 

select distinct object_type
from user_objects
;

But..... USER_OBJECTS has only these types FUNCTION
INDEX, PACKAGE, PACKAGE BODY, PROCEDURE, SEQUENCE, TABLE, TRIGGER, VIEW because select distinct object_type from user_objects; returned them. So this query is not giving my the constraints at all.

Is there a way to get all constraints from Oracle? Which Oracle view should I use?

Upvotes: 2

Views: 5383

Answers (2)

Codo
Codo

Reputation: 78805

Constraints aren't objects. So they're in a different view, namely USER_CONSTRAINTS. For foreign constraints, you'll need a self join:

select * from user_constraints c
left join user_constraints r on r.owner = c.r_owner and r.constraint_name = c.r_constraint_name
where c.constraint_type = 'R';

Some details can also be found in USER_CONS_COLUMNS.

Upvotes: 3

Michael Pakhantsov
Michael Pakhantsov

Reputation: 25370

   select * from user_constraints

Upvotes: 5

Related Questions