?
Solved

get all table names for a given schema

Posted on 2011-05-03
7
Medium Priority
?
1,131 Views
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

0
Comment
Question by:PearlJamFanatic
7 Comments
 
LVL 24

Accepted Solution

by:
johanntagle earned 336 total points
ID: 35605686
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.
0
 
LVL 23

Assisted Solution

by:OP_Zaharin
OP_Zaharin earned 668 total points
ID: 35629447
- 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
0
 
LVL 14

Assisted Solution

by:Jan Franek
Jan Franek earned 332 total points
ID: 35687090
For Sybase try this:

select name from sysobjects where uid = user_id( 'abcdefg' )
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 2

Assisted Solution

by:drittenh
drittenh earned 332 total points
ID: 35688287
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'
0
 
LVL 23

Assisted Solution

by:OP_Zaharin
OP_Zaharin earned 668 total points
ID: 35688314
- 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';
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 332 total points
ID: 35689777
You don't necessarily need access as SYS or SYSTEM to query the DBA_TABLES view. You just need the select any dictionary privilege.
0
 

Author Comment

by:PearlJamFanatic
ID: 35704049
SELECT table_name
  FROM all_tables where owner='abcdefg' worked for me.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
What we learned in Webroot's webinar on multi-vector protection.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

807 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