Re: dba_tab_columns

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: 14 Jul 2003 22:24:29 -0700
Message-ID: <1a75df45.0307142124.f401b4f@posting.google.com>

gabsaga_tata_at_hotmail.com (Taji) wrote in

> Does anyone happen to know the exact select statement that the
> 'describe' command emulates? I tried something like the following but
> it didn't exactly work 'cause when the data_type is a VARCHAR, it
> doesn't show the number of characters.

Try something like the following. %3 is the owner and %1 is the tablename. This does not do partition, iot's and is also missing constraints, support for more exotic data types and so on. But it does the vanilla basics.

You may want to change the chr(13) into a proper linefeed too.

CREATE TABLE %3.%1 ' "LINE"
FROM dual
UNION ALL
SELECT
'('

FROM dual
UNION ALL
SELECT
  *
FROM (
SELECT
' ' || RPAD( LOWER(t1.column_name), 30 ) ||
  RPAD(
    DECODE( t1.data_type,

      'DATE',    t1.data_type,
      'NUMBER',  t1.data_type ||'(' || t1.data_precision ||
        DECODE( t1.data_scale,0, ')', ',' || t1.data_scale || ')' ) ,
      t1.data_type ||'(' || t1.data_length || ')'
           ) , 20 ) ||

  DECODE( t1.nullable, 'Y', '',
'NOT NULL') ||

  DECODE( t2.maxcol, NULL, ',' , '' ) "COLUMN" FROM all_tab_columns t1,

     (SELECT MAX(column_id) MAXCOL FROM all_tab_columns
      WHERE table_name = '%1'
      AND   owner = '%3' )  t2
WHERE t1.table_name = '%1'

AND t1.owner = '%3'
AND t1.column_id = t2.maxcol (+)
ORDER BY t1.column_id )
UNION ALL
SELECT
')'

FROM dual
UNION ALL
SELECT
'TABLESPACE ' || lower(a.tablespace_name)
FROM all_tables a
WHERE a.table_name = '%1'
AND a.owner = '%3'
UNION ALL
SELECT
  DECODE( a.logging, 'YES', 'LOGGING', 'NOLOGGING' ) || chr(13) ||
'PCTFREE ' || a.pct_free || chr(13) ||
'PCTUSED ' || a.pct_used || chr(13) ||
'INITRANS ' || a.ini_trans || chr(13) ||
'MAXTRANS ' || a.max_trans || chr(13) ||
'PARALLEL ( degree ' || LTRIM(a.degree) || ' instances ' ||
LTRIM(a.instances) || ' )' || chr(13) ||
'STORAGE(' || chr(13) ||
' initial ' || a.initial_extent || chr(13) ||
' next ' || a.initial_extent || chr(13) ||
' minextents ' || a.min_extents || chr(13) ||
' maxextents ' || a.max_extents || chr(13) ||
' pctincrease ' || a.pct_increase || chr(13) ||
' freelists ' || a.freelists || chr(13) ||
' buffer_pool ' || a.buffer_pool || chr(13) ||
' freelist groups ' || a.freelist_groups || chr(13) ||
' )'

FROM all_tables a
WHERE a.table_name = '%1'
AND a.owner = '%3'

--
Billy

Received on Tue Jul 15 2003 - 00:24:29 CDT