get all table names for a given schema

Posted on 2011-05-03
Last Modified: 2012-06-27
need sql for to retrieve the informationf rom sybase 12.5, sybase 15 and oracle 11g databases.
The name of the schema is 'abcdefg'.

The information i need is available in TOAD. Schema browser--->select the schema 'abcdefg' from the list box on top--->the tables of this schema ar listed in the 'table' panel on the left.

the sql query should return the same list of table that is visible through toad.

already tried

SELECT table_name FROM user_tables

SELECT table_name FROM all_tables
select * from tabs
select * from abcdefg.ALL_TABLES

Question by:PearlJamFanatic
    LVL 24

    Accepted Solution

    I don't use TOAD but for Oracle, what exactly does TOAD give that doesn't appear in:

    select TABLE_NAME from user_tables;
    select TABLE_NAME from all_tables where owner='SCHEMAOWNER';

    Let me know what types of objects those are so I know how to query it.
    LVL 23

    Assisted Solution

    - do you get any error message? or it display number of tables which is only for the schema you are login as?

    - in Oracle, login as SYS or SYSTEM and try the following again:

    SELECT * FROM tab
    SELECT * from all_tables
    LVL 14

    Assisted Solution

    For Sybase try this:

    select name from sysobjects where uid = user_id( 'abcdefg' )
    LVL 2

    Assisted Solution

    For sybase 12 and 15, the user-defined table names are retrieved by:

    select name from sysobjects where user_name(uid) = 'abcdefg' and type = 'U'
    LVL 23

    Assisted Solution

    - to further explain in oracle again:

    - if you do not have access to DBA_TABLES, you can see all the tables that your account has access to through ALL_TABLES view:

    SELECT owner, table_name
      FROM all_tables

    - if you have access as SYS or SYSTEM account, you can query to DBA_TABLES view and specify the where clause on the schema owner as you required:

    SELECT owner, table_name
      FROM dba_tables
      WHERE owner='abcdefg';
    LVL 31

    Assisted Solution

    You don't necessarily need access as SYS or SYSTEM to query the DBA_TABLES view. You just need the select any dictionary privilege.

    Author Comment

    SELECT table_name
      FROM all_tables where owner='abcdefg' worked for me.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
    This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    6 Experts available now in Live!

    Get 1:1 Help Now