Re: USER_TAB_COLUMNS vs DESCRIBE

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Thu, 2 Dec 1999 09:19:16 +0100
Message-ID: <8259vh$7b87$1@oceanite.cybercable.fr>

Here's a script that gives you all your tables with their columns something like desc:

Set feedback OFF
Set heading OFF
Set linesize 2000
Set pagesize 0
Set trimout ON
Select decode(col.column_id, 1, '

'||col.table_name||'
', '')||
'   '||col.column_name||' '||col.data_type||
    decode(col.data_type,


           'NUMBER', decode(col.data_precision,


                            NULL, decode(col.data_scale, NULL, '', '(38)'),
                            '('||col.data_precision||
                                 decode(col.data_scale, NULL, ')',
                                        ','||col.data_scale||')')),


           'CHAR', '('||col.data_length||')',


           'VARCHAR2', '('||col.data_length||')',


           'RAW', '('||col.data_length||')'


           )||

    decode(col.nullable, 'N', ' NOT NULL', '') from user_tab_columns col
order by col.table_name, col.column_id
/

--
Have a nice day
Michel

Steve Lucord <steve.a.lucord_at_lmco.com> a écrit dans le message : 38458744.EFE795CC_at_lmco.com...

> I am attempting to create a data dictionary report via SQL*PLUS.  Using
> the USER_TAB_COLUMNS view I am able to get the following output:
>
>
> Data            Data      Data
> Table Name                         Column
> Name                                      Type            Length   Scale
> Null
> -------------------------       ------------------------------ ---------
> ------          -----     ----   ----
> WTW_PERFORMANCE    ACTUAL_90_DAY_RETENTION     NUMBER   22
> 2       Y
>
> DESCRIBE provides the following information for same table/column name:
>
>  Name                                                Null?        Type
>  -------------------------------           --------    ----
>
>  ACTUAL_90_DAY_RETENTION                  NUMBER(3,2)
>
> Does anyone know a way to format the Data Type/Data Length/Data Scale as
> NUMBER(3,2) using SQL*PLUS?
>
> Thanks,
>
> Steve Lucord
>

Received on Thu Dec 02 1999 - 02:19:16 CST