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 |
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> |
http://onewebsql.com/blog/list-all-tables
