Listing all columns from an Oracle database

In this short post, we are going to learn how to list all columns of every table in an Oracle database.
We want to get the name of the column, the name of the table it belongs to and the type of date for that column.

Query

We will be using a simple SQL query, like this one:

set linesize 280
set pagesize 100
col table_name for a30
col column_name for a30
col data_type for a30
select table_name, column_name, data_type
from ALL_TAB_COLUMNS;

In the example we are using “ALL_TAB_COLUMNS” to get the info, if you have any problem with this, it may be because of low permissions in the database, use “USER_TAB_COLUMNS” instead.
In the example, all code before “select” is just some typical modifications on the “sqlplus” output format, you can ignore or modify them at your will.

Leave a Reply

Your email address will not be published.