Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2010
    Posts
    64

    Unanswered: Listing all tables in schema along with synonyms ,if any

    hi,

    Please could someone help me with the following :-
    Listing all tables in schema along with synonyms ,if any.

    I have tried DBA_SYNONYMS , USER_SYNONYMS , SYNONYMS etc.

    I think when i am using the above i am missing those tables which are not having
    any SYNONYMS. i need a complete list of all tables on a schema along with synonyms if any. If it is not there then it should report blank

    please advise

    thanks
    subhotech

  2. #2
    Join Date
    Jan 2010
    Posts
    64
    I tried this query and i get some desirable results but i am not sure if i am missing anything :-

    select a.owner , a.table_name , b.owner as "Synonym scope"
    from all_tables a , DBA_SYNONYMS b
    where a.table_name = b.table_name(+) and a.owner = b.table_owner(+) ;

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I think when i am using the above i am missing those tables which are not having any SYNONYMS.

    Code:
      1  select ut.table_name, us.synonym_name from user_tables ut, user_synonyms us
      2* where ut.table_name = us.table_name(+)
    SQL> /
    
    TABLE_NAME                     SYNONYM_NAME
    ------------------------------ ------------------------------
    REGIONS
    LOCATIONS
    DEPARTMENTS
    JOBS
    EMPLOYEES
    JOB_HISTORY
    COUNTRIES
    
    7 rows selected.
    Last edited by anacedent; 11-07-12 at 22:38.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Anacedent, please use LEFT OUTER JOIN instead of the outdated (and limited) (+) operator
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Use the following query so that you will capture any public synonyms

    Code:
    select ut.table_name, us.owner||'.'||us.synonym_name  syn
    from user_tables ut, all_synonyms us
    where ut.table_name = us.table_name(+)
    and user = us.table_owner(+);
    Last edited by beilstwh; 11-08-12 at 17:04.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Listing all tables in schema along with synonyms ,if any.
    What about private SYNONYM that you can not access & therefore not report their existence?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    They will only not show if the user has no access to them.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •