Get Database's Tables

Question

I want to write a query to get the names of tables of a specific database, but I don't know how can write it.

I want to execute this query for MS Access 2007 and Oracle 11g.

Thanks

Are you using a database abstraction layer, like the JDBC or perl's DBI? Many such frameworks have methods to enumerate a database's catalogs, schema and objects (tables, views, etc.), which methods are implemented by specific drivers. — pilcrow
– pilcrow, Commented Aug 14, 2010 at 14:19
I think he just means a query to get list of the tables back. — user201788
– user201788, Commented Aug 14, 2010 at 14:23
@AJ, right. This functionality already exists without the coder having to worry about this or that version of this or that RDBMS (e.g., $dbh->tables() under the DBI). — pilcrow
– pilcrow, Commented Aug 14, 2010 at 14:30

Paul Sasik · Accepted Answer · 2010-08-14 14:33:07Z

2

If you want raw, direct queries:

For Oracle:

SELECT * FROM user_tables

For MS Access:

SELECT * FROM  MSysObjects  WHERE [Type] In (1, 4, 6)

(sorting and advanced filtering omitted for brevity.)

answered Aug 14, 2010 at 14:33

Paul Sasik

81.9k23 gold badges152 silver badges194 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Depending on context, you may get a permissions error on the MS Access query.