I would like to see the constraints defined in a synonym of a table.
When a type
SQL> DESCRIBE table_name;
It shows me only the number of each column, the not null flag and the size. How can I know all the constraints for each column? How can I know also if the synonyms are either public or private?
Thanks, Alvaro.
SOLUTION
With @Aramillo helps and adapting it to my needs, I figure out how to do it. I want to see the Constraints defined for a table but not according to the USER. There are two options, with ALL_CONSTRAINTS and ALL_CONS_COLUMNS.
ALL_CONSTRAINTS has not the column COLUMN_NAME in order to know the column's name which the constraints is set to. [ALL_CONS_COLUMNS] has.
Query:
SELECT * FROM all_cons_columns WHERE table_name = 'table_name';
About the private or public synonym, as the links above say,
ALL_CONSTRAINTS/ALL_CONS_COLUMNS describes constraint definitions on tables accessible to the current user.
So I guess if it shows data either the synonym is public or you are the correct user for the private synonym.
2 Answers
This query gives you all constraints of a synonym with name REGION_SYN_P for the current user:
SELECT constraint_name, c.table_name, synonym_name, S.OWNER FROM user_constraints C, all_SYNONYMS S WHERE C.TABLE_NAME = S.TABLE_NAME AND S.TABLE_OWNER = C.OWNER AND S.Synonym_name = 'REGION_SYN_P' If you want to know if is public or not, you just have to check owner column, in case it's a public synonym the owner will be PUBLIC.
You can use all_constraints instead user_constraints if you need query the constraints in other schemas. I hope this helps.
the following is for Oracle. Query the so called static data dictionary: oracle constrains
work it out with DBA_CONSTRAINTS USER_CONSTRAINTS or DBA_CONS_COLUMNS USER_CONS_COLUMNS
6
