Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL command to show tables

 
Matthew Phillips
Ranch Hand
Posts: 2676
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Is there an SQL command that will show the names of all of the tables in a database?
 
Michael Matola
whippersnapper
Ranch Hand
Posts: 1819
4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2676
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am using Postgre SQL.
 
Marilyn de Queiroz
Sheriff
Posts: 9063
12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In MySQL it is "SHOW DATABASES ;"
 
Michael Matola
whippersnapper
Ranch Hand
Posts: 1819
4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"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
Posts: 1819
4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2676
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Select * from pg_class did the trick. Thanks.
 
Reid M. Pinchback
Ranch Hand
Posts: 775
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Sameer Jamal
Ranch Hand
Posts: 1870
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What abt "select * from tab"
 
Eric Rybski
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
A solution a bit cleaner to work with than pg_class:
SELECT * FROM pg_tables;
-Eric
 
shijuu ev
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
why for MS Sql 2000
 
Steven Broadbent
Ranch Hand
Posts: 400
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
select * from dba_tables in Oracle shows all tables
 
Mike Putnam
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic