Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    5

    Unanswered: select * from shema

    hi

    i would like to select all tables which are in schema sapr3


    select * from <table> where <shema> = "sapr3";


    in which table and field are this informations.

    in db2 is: select * from syscat where shema="sapr3;

  2. #2
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246
    There are many, many ways to do this.

    1. select tname from tab;
    2. select table_name from user_tables;
    3. select table_name from all_tables where owner = 'YOURSCHEMA';
    4. select object_name from all_objects where object_type = 'TABLE' and owner = 'YOURSCHEMA';
    5. replace "TABLES" with "SEGMENTS" in some queries. For example, "select segment_name from user_segments;" would show you all segments associated with all of your tables, in case a table has more than one segment in the case of LOBs or Partitions.

    When connected to SQL*Plus or other SQL interface program, type the command DESC ALL_TABLES to see what columns are available.

    If you are connected as YOURSCHEMA then you can replace it in the query with the keyword USER. Just like in SQL you can say SELECT USER FROM DUAL, you could say ...
    select table_name from all_tables where owner = (SELECT USER FROM DUAL);
    which is the same as saying ...
    select table_name from all_tables where owner = USER ;

    -Mark

  3. #3
    Join Date
    Oct 2003
    Posts
    5
    thanks but i need a output to a file
    without this:


    TABLE_NAME
    ------------------------------

    only:

    table
    table
    table
    table
    table
    table


    select table_name from all_tables where owner = 'SAPR3' ;

  4. #4
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208
    SQL>Set Heading Off

    will turn off the headings in SQL plus and only return the data.

    If you want to send it to an output file, use

    SQL>Set Heading Off
    SQL>Spool output.txt
    SQL>Select Table_Name
    From All_Tables
    Where Owner = 'SAPR3';

    SQL>Spool Off

  5. #5
    Join Date
    Oct 2003
    Posts
    5
    thanx a lot

Posting Permissions

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