Oracle Synonym Table Describe Constraints
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...
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
Sarah Jenkins is a veteran tech journalist with over 12 years of experience covering artificial intelligence, mobile innovations, and digital ethics. Her insights have appeared in leading technology publications worldwide.
