Oracle obtains the structure information of a table containing constraints.

Environment Oracle 9 Release 2

User sys

Test Table Name ATTRIBUTE_TRANSFORMATIONS $

Tables and views in the used data dictionary

USER_TAB_COLUMNS: basic organization information of the User table

User_constraints: basic user constraint information

User_cons_columns: user constraints and field information

Select a. column_name, a. data_type, a. data_length, B. constraint_type, B. r_table_name, B. r_column_name
From
(Select column_name, Data_type, data_length from USER_TAB_COLUMNS WHERE USER_TAB_COLUMNS.TABLE_NAME = 'attribute _ TRANSFORMATIONS $ '),
(Select a. column_name, a. constraint_type, B. table_name r_table_name, B. column_name r_column_name
From
(Select a. constraint_name, a. constraint_type, a. r_constraint_name, B. table_name, B. column_name
From user_constraints a, user_cons_columns B
WHERE a. constraint_name = B. constraint_name and a. table_name = 'attribute _ TRANSFORMATIONS $ ')
Left join user_cons_columns B on a. r_constraint_name = B. constraint_name
) B
Where a. column_name = B. column_name (+)

Running result