dba_tab_columns
Name Null? Type
----------------------- -------- --------------
OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
COLUMN_NAME NOT NULL VARCHAR2(30)
DATA_TYPE VARCHAR2(106)
DATA_TYPE_MOD VARCHAR2(3)
DATA_TYPE_OWNER VARCHAR2(30)
DATA_LENGTH NOT NULL NUMBER
DATA_PRECISION NUMBER
DATA_SCALE NUMBER
NULLABLE VARCHAR2(1)
COLUMN_ID NUMBER
DEFAULT_LENGTH NUMBER
DATA_DEFAULT LONG
NUM_DISTINCT NUMBER
LOW_VALUE RAW(32)
HIGH_VALUE RAW(32)
DENSITY NUMBER
NUM_NULLS NUMBER
NUM_BUCKETS NUMBER
LAST_ANALYZED DATE
SAMPLE_SIZE NUMBER
CHARACTER_SET_NAME VARCHAR2(44)
CHAR_COL_DECL_LENGTH NUMBER
GLOBAL_STATS VARCHAR2(3)
USER_STATS VARCHAR2(3)
AVG_COL_LEN NUMBER
CHAR_LENGTH NUMBER
CHAR_USED VARCHAR2(1)
V80_FMT_IMAGE VARCHAR2(3)
DATA_UPGRADED VARCHAR2(3)
Information about every table or view column in the database. For example:
create table andy.test_table(
id number primary key
, text varchar2(100)
)
/
Table created.
select column_name
, data_type
, data_length
, nullable
from dba_tab_columns
where table_name = 'TEST_TABLE'
/
COLUMN_NAME DATA_TYPE DATA_LENGTH N
------------------ -------------------- ----------- -
ID NUMBER 22 N
TEXT VARCHAR2 100 Y
You can also use it to search for tables that contain columns with certain names:
set pages 999 lines 100
col tab format a60
col column_name format a20
select owner || '.' || table_name as tab
, column_name
from dba_tab_columns
where column_name like upper('&col')
/
For more useful DBA queries click here.
| 