Results 1 to 8 of 8

Thread: data dictionary

  1. #1
    Join Date
    Oct 2004
    Posts
    54

    Unanswered: data dictionary

    i'm using oracle 8i, i just would ike to know using the data dictionary...
    what is the sql script to know if the schema belongs to the database,
    if the table belongs to the schema and if the field belongs to the table.

    thank you guys in advance....

  2. #2
    Join Date
    Jan 2004
    Posts
    492
    Look in the user_tables table for all tables owned by the user logged in
    Look in the all_tables table for all tables in the database. There are columns for owner and etc.

    Look in the user_tab_columns or all_tab_columns for columns in the tables (use table_name = 'your_table')
    Oracle OCPI (Certified Practicing Idiot)

  3. #3
    Join Date
    Oct 2004
    Posts
    54
    thanks for your help....
    hhmm about schema...
    i'm trying to experiment on schema_name
    and all_schema but it seems there is no
    such thing....

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    FYI,
    The ALL_TABLES view is a list of all tables that the user has access to. The DBA_TABLES is the view that shows ALL tables, but that view is only visable to people with the DBA priviledges. If you want a list of all schemas that own tables that the user can access then issue the following select.

    select distinct owner from all_tables;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Oct 2004
    Posts
    54
    thanks for the help...
    hhmm another question.. how about if i want to list
    all talbes belonging to a schema... how can i go about this one...
    thanks...

  6. #6
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    select table_name from all_tables where owner = 'schemaname';

    HTH
    Gregg

  7. #7
    Join Date
    Jan 2004
    Posts
    492
    It would be:

    Code:
    select table_name
    from all_tables
    where owner = 'HR' -- or whatever schema you want.
    Oracle OCPI (Certified Practicing Idiot)

  8. #8
    Join Date
    Oct 2004
    Posts
    54
    thanks for all your help guys. !!!!!!!!

Posting Permissions

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