Search code examples
sqloracle-databaseconstraintssqlplussynonym

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 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.

Solution

  • 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.