How to List All Tables in Oracle, MySQL, DB2 and PostgreSQL
In this tutorial, We will learn how to connect, list all tables in Oracle, MySQL, DB2, PostgreSQL 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, In this post placed all most popular databases.
Now we will see the SQL statements on how to connect to each database such as Oracle, MySQL, DB2 and PostgreSQL, all tables owned by the current user and in the database, what are the tables accessed by the current user and how to see the table structure.
First, we will start with Oracle database. Next we will see the remaining MySQL, DB2 and PostgreSQL.
List All Tables in Oracle:
Oracle Wikipedia.
How to Connect to the Oracle database:
sqlplus username/password@database-name
To list all tables owned by the current user:
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 or 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.
All tables in Oracle Documentation.
To describe a table or type:
desc table-name;
List All Tables in MySQL:
How to Connect to the MySQL database:
mysql [-h hostname] [-u username] [-pmypass] database-name
mysql -h localhost -u myname 1 mydb
MySQL official manual reference.
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
List All Tables in DB2:
Connect to the DB2 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
List All Tables in PostgreSQL:
Connect to the PostgreSQL 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
Please leave your understandings in comments to become better.
0 Comments