Thursday, April 14, 2016

How to List All Tables and Describe Tables in Oracle, MySQL, DB2 and PostgreSQL

You often want to list all tables in a database or list columns in a table. Obviously, every database has its own syntax to list the tables and columns. Well, here it is -- all in one place for the most popular databases.

Oracle

Connect to the database:
?
1
sqlplus username/password@database-name
To list all tables owned by the current user, type:
?
1
select tablespace_name, table_name from user_tables;
To list all tables in a database:
?
1
select tablespace_name, table_name from dba_tables;
To list all tables accessible to the current user, type:
?
1
select tablespace_name, table_name from all_tables;
You can find more info about views all_tablesuser_tables, and dba_tables in Oracle Documentation. To describe a table, type:
?
1
desc

MySQL

Connect to the database:
?
1
mysql [-u username] [-h hostname] database-name
To list all databases, in the MySQL prompt type:
?
1
show databases
Then choose the right database:
?
1
use <database-name>
List all tables in the database:
?
1
show tables
Describe a table:
?
1
desc <table-name>

DB2

Connect to the database:
?
1
db2 connect to <database-name>
List all tables:
?
1
db2 list tables for all
To list all tables in selected schema, use:
?
1
db2 list tables for schema <schema-name>
To describe a table, type:
?
1
db2 describe table <table-schema.table-name>

PostgreSQL

Connect to the database:
?
1
psql [-U username] [-h hostname] database-name
To list all databases, type either one of the following:
?
1
2
\l
\list
To list tables in a current database, type:
?
1
\dt
To describe a table, type:
?
1
\d <table-name>
http://onewebsql.com/blog/list-all-tables