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:

sqlplus username/password@database-name

To list all tables owned by the current user, type:

select tablespace_name, table_name from user_tables;

To list all tables in a database:

select tablespace_name, table_name from dba_tables;

To list all tables accessible to the current user, type:

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:

desc <table_name>

MySQL

Connect to the database:

mysql [-u username] [-h hostname] database-name

To list all databases, in the MySQL prompt type:

show databases

Then choose the right database:

use <database-name>

List all tables in the database:

show tables

Describe a table:

desc <table-name>

DB2

Connect to the database:

db2 connect to <database-name>

List all tables:

db2 list tables for all

To list all tables in selected schema, use:

db2 list tables for schema <schema-name>

To describe a table, type:

db2 describe table <table-schema.table-name>

PostgreSQL

Connect to the database:

psql [-U username] [-h hostname] database-name

To list all databases, type either one of the following:

\l
\list

To list tables in a current database, type:

\dt 

To describe a table, type:

\d <table-name>

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

Vertabelo