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

ref:http://onewebsql.com/blog/list-all-tables

by Agnieszka Kozubek, Java Programmer at e-point SA, 22 November 2012

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_tables, user_tables, and dba_tables in Oracle Documentation. To describe a table, type:

?

1	desc <table_name>

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>

Would you like more "cheatsheet" posts like this? Let me know in the comments!