gabsaga_tata@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