How to List All Tables in Oracle, MySql, Teradata, DB2 and PostgreSQL

Very Often we may need to check avaiable tables in a database or list columns in a table.every database has its own syntax to list the tables and columns.Here we will see How to List All Tables in Oracle, MySql, Teradata, DB2 and PostgreSQL.

Oracle

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;

To describe a table, type:

desc <table_name>;

MySQL

To list all databases, in the MySQL prompt type:

show databases

Then choose the database:

use <database-name>

List all tables in the database:

show tables

Describe a table:

desc <table-name>

DB2

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

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>

Teradata

List all tables:

select * from DBC.TABLES[V] where tablename=<tablename>;

To describe a table, type:

show table <databasename.tablename>;