Wednesday, June 18, 2008

How to get table structure in Oracle?

There is one data dictionary table called user_tab_columns which store metadata about table.
In this the column_name, data_type and data_length are usually we want.
For this purpose.

SQL> set lines 200
SQL> select column_name,data_length,data_type from user_tab_columns where table_name = 'DEPT';

Output of above query will show following metadata of DEPT table.

COLUMN_NAME DATA_LENGTH DATA_TYPE
------------------------------ ----------- -------------------
DEPTNO 22 NUMBER
DNAME 14 VARCHAR2
LOC 13 VARCHAR2

No comments: