Pages

Wednesday, September 5, 2018

How to List All Tables, Describe 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.

oracle and mysql all_tables examples

show all tables MySQL, show all tables in oracle, show all tables Postgres, show all tables, show all tables in database SQL, show all tables SQLite, show all tables in a database, show all tables in Cassandra, show all tables in hive, show all tables in schema Postgres, show all tables SQL, show all tables and columns MySQL

 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, this post placed all most popular databases.


 First, we will start with the Oracle database. Next, we will see the remaining MySQL, DB2, and PostgreSQL.

Oracle:

Oracle Wikipedia.

Image result for connecting to a oracle database




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>



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>

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>

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.

No comments:

Post a Comment

Please do not add any spam links in the comments section.