This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes General Computing and the fly likes SQL command to show tables Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Engineering » General Computing
Bookmark "SQL command to show tables" Watch "SQL command to show tables" New topic
Author

SQL command to show tables

Matthew Phillips
Ranch Hand

Joined: Mar 09, 2001
Posts: 2676
Is there an SQL command that will show the names of all of the tables in a database?


Matthew Phillips
Michael Matola
whippersnapper
Ranch Hand

Joined: Mar 25, 2001
Posts: 1745
    
    2
System tables are product specific, I believe.
In Oracle
select * from all_tables ;
(all_views for views, etc.)
What product are you using?
[ May 01, 2002: Message edited by: Michael Matola ]
Matthew Phillips
Ranch Hand

Joined: Mar 09, 2001
Posts: 2676
I am using Postgre SQL.
Marilyn de Queiroz
Sheriff

Joined: Jul 22, 2000
Posts: 9044
    
  10
In MySQL it is "SHOW DATABASES ;"


JavaBeginnersFaq
"Yesterday is history, tomorrow is a mystery, and today is a gift; that's why they call it the present." Eleanor Roosevelt
Michael Matola
whippersnapper
Ranch Hand

Joined: Mar 25, 2001
Posts: 1745
    
    2
"show databases;" in MySQL gets you a list of databases. To see a listing of tables, once you have done a "use < database name >;" to chose a database, you can do a "show tables;" for the tables in that particular database.
Michael Matola
whippersnapper
Ranch Hand

Joined: Mar 25, 2001
Posts: 1745
    
    2
Just found these Postgres docos. Looks like "pg_class" holds tables. Just give "select * from pg_class;" a try.
Coupla other sources here and here suggest the same and give a couple more options.
[ May 01, 2002: Message edited by: Michael Matola ]
Matthew Phillips
Ranch Hand

Joined: Mar 09, 2001
Posts: 2676
Select * from pg_class did the trick. Thanks.
Reid M. Pinchback
Ranch Hand

Joined: Jan 25, 2002
Posts: 775
Originally posted by Michael Matola:

In Oracle
select * from all_tables ;

A note for the Oracle users: this will only list all the tables to which you have access. You only get all the tables in the database if the user you are logged in as has access to all the tables in the database. There is probably some equivalent command using the V$ tables, but I don't use them so I don't know the equivalent.


Reid - SCJP2 (April 2002)
Sameer Jamal
Ranch Hand

Joined: Feb 16, 2001
Posts: 1870
What abt "select * from tab"
Eric Rybski
Greenhorn

Joined: May 01, 2003
Posts: 1
A solution a bit cleaner to work with than pg_class:
SELECT * FROM pg_tables;
-Eric
shijuu ev
Greenhorn

Joined: Jul 24, 2003
Posts: 1
why for MS Sql 2000
Steven Broadbent
Ranch Hand

Joined: Dec 10, 2002
Posts: 400
select * from dba_tables in Oracle shows all tables


"....bigmouth strikes again, and I've got no right to take my place with the human race...."<p>SCJP 1.4
Mike Putnam
Greenhorn

Joined: Oct 05, 2004
Posts: 1
In response to:

shijuu ev
greenhorn
Member # 53543
posted July 24, 2003 02:21 AM
----
why for MS Sql 2000
----
The following stored procedure entered in to "Query Analyzer" will show the tables:

sp_tables
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL command to show tables
 
Similar Threads
What is the SQL command to list all tables in a access database(mdb)
how to write Query in LIKE Clause.
sql doubt
Need Help with pl/sql (Compilation Error)
what is the command in oracle to show databases?