How to get constraint information

Below is a quick SQL to get all constraint information on a particular table:

SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner, cons.constraint_type FROM all_constraints cons, all_cons_columns cols

WHERE cols.table_name = 'DEPT'

AND cons.constraint_name = cols.constraint_name

AND cons.owner = cols.owner

ORDER BY cols.table_name, cols.position;

i. For cols.table_name you can give table for which you want retrieve the constraint information.
ii. For information on particular type constraints only on a table i.e., if you need the information regarding only

"Primary Key" constraints use below SQL:

SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner

FROM all_constraints cons, all_cons_columns cols

WHERE cols.table_name = 'DEPT'

AND cons.constraint_type = 'P'

AND cons.constraint_name = cols.constraint_name

AND cons.owner = cols.owner

ORDER BY cols.table_name, cols.position;
Below are some of different options that you can give for cols.table_name to information regarding particular type of constraints on a table. Type of constraint definition:
•C (check constraint on a table)
•P (primary key)
•U (unique key)
•R (referential integrity)
•V (with check option, on a view)
•O (with read only, on a view)
Note: The above SQL's are for informational only that I used on a daily basis