109

Oracle constraint name is known.

How do I find the name of the table for which this constraint is applied?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
sergionni
  • 13,290
  • 42
  • 132
  • 189

3 Answers3

201
SELECT owner, table_name
  FROM dba_constraints
 WHERE constraint_name = <<your constraint name>>

will give you the name of the table. If you don't have access to the DBA_CONSTRAINTS view, ALL_CONSTRAINTS or USER_CONSTRAINTS should work as well.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • not working, getting error "Invalid object name 'USER_CONSTRAINTS'". please help. – Jitendra Pancholi Feb 05 '13 at 09:24
  • 9
    @JitendraPancholi - `select table_name from user_constraints where constraint_name = 'xxx';` should work for any user. Are you sure you don't have a typo in your query? What does `select owner, object_name from all_objects where object_name = 'USER_CONSTRAINTS'` return for you? – Justin Cave Feb 05 '13 at 14:44
  • Add owner name in the query too... SELECT owner, table_name FROM dba_constraints WHERE constraint_name = <> and owner = <> – JavaDragon Sep 26 '15 at 06:39
18

ALL_CONSTRAINTS describes constraint definitions on tables accessible to the current user.

DBA_CONSTRAINTS describes all constraint definitions in the database.

USER_CONSTRAINTS describes constraint definitions on tables in the current user's schema

Select CONSTRAINT_NAME,CONSTRAINT_TYPE ,TABLE_NAME ,STATUS from 
USER_CONSTRAINTS;
Nima Derakhshanjan
  • 1,380
  • 9
  • 24
  • 37
Suprriya
  • 181
  • 1
  • 2
-4
SELECT constraint_name, constraint_type, column_name
from user_constraints natural join user_cons_columns
where table_name = "my_table_name";

will give you what you need

Van Gogh
  • 465
  • 1
  • 7
  • 8
  • this answer is from known table name to return constraint name, question says contraint name is known need to get table name – Jim Ford Apr 24 '14 at 18:06