Columns

March 7th, 2012

Table columns

COLUMN column_name         FORMAT A20                HEADING "Column Name"
COLUMN data_type           FORMAT A25                HEADING "Data Type"
COLUMN nullable            FORMAT A13                HEADing "Null?"

SELECT
    column_name
  , DECODE(nullable, 'Y', ' ', 'NOT NULL') nullable
  , DECODE(data_type
               , 'RAW',      data_type || '(' ||  data_length || ')'
               , 'CHAR',     data_type || '(' ||  data_length || ')'
               , 'VARCHAR',  data_type || '(' ||  data_length || ')'
               , 'VARCHAR2', data_type || '(' ||  data_length || ')'
               , 'NUMBER', NVL2(   data_precision
                                 , DECODE(    data_scale
                                            , 0
                                            , data_type || '(' || data_precision || ')'
                                            , data_type || '(' || data_precision || ',' || data_scale || ')'
                                   )
                                 , data_type)
               , data_type
    ) data_type
FROM
    dba_tab_columns
WHERE
      owner      = UPPER('&schema_name')
  AND table_name = UPPER('&table_name')
ORDER BY
    column_id
/


Comments are closed.