1

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.

alvgarvilla
  • 1,026
  • 12
  • 25

2 Answers2

2

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.

Aramillo
  • 3,176
  • 3
  • 24
  • 49
0

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

stack link for Oracle

Community
  • 1
  • 1
ninjabber
  • 371
  • 2
  • 7
  • 1
    There is no `information_schema` in Oracle. And there is no `use` statement. –  Sep 22 '14 at 14:47
  • When I type INFORMATION_SCHEMA:`SQL> use INFORMATION_SCHEMA; unknown command beginning "use INFORM..." - rest of line ignored.` as @a_horse_with_no_name says, it seems that there is not such table in Oracle (neither `use` statement) – alvgarvilla Sep 22 '14 at 14:52
  • and instead of `use` what is used with Oracle? – alvgarvilla Sep 22 '14 at 15:31
  • click on the link I provided (oracle constrains) but you will have to understand the data dictionary which is the equivalent of information_schema – ninjabber Sep 22 '14 at 15:32
  • -1 for not getting rid of the MySQL specific code in a question tagged Oracle – Frank Schmitt Sep 23 '14 at 10:43
  • as requested. you can keep your -1. I'm not gonna cry ;) – ninjabber Sep 23 '14 at 17:00