Re: SQL*Plus

From: Evan J. Greene <ejg_at_sequent.com>
Date: Thu, 10 Jun 93 15:41:41 GMT
Message-ID: <1993Jun10.154141.773_at_sequent.com>

In article <1v7a5j$sle_at_spock.dis.cccd.edu> paulk_at_spock.dis.cccd.edu (Paul Krikorian) writes:
>
> {stuff deleted...}
>
> I would like to create an SQL*Plus script that would use the data
> stored in the system table USER_TAB_COLUMNS to print a report in the
> following format:
>
> Table Column Null? Type
> -------------------- ------------------------------- -------- ----
> table_1 column_1 NOT NULL NUMBER
> column_2 DATE
> column_3 NUMBER
> column_4 CHAR(x)
>
> table_2 column_1 CHAR(x)
> .
> .
> .
>
> table_n column_1 CHAR(x)
>
>
> {more stuff deleted...}
>

Here you go:
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * column table_name head 'Table' format A20 column column_name head 'Column'
column dnull head 'Null?'
column dtype head 'Type' format A12
break on table_name skip 1
select table_name, column_name,

       decode(nullable, 'N', 'NOT NULL', '        ') dnull,
       decode(data_type, 'CHAR', 'CHAR('||data_length||')', data_type) dtype

from user_tab_columns
order by table_name, column_id
/
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * This won't work properly on V7 as it stands (CHAR vs. VARCHAR2, etc.)

Good luck.

     +====================================================================+
     |  +-----+   From:     Evan J. Greene, Sequent Computer Systems      |
     |  |    #|             Professional Services Group, Boston           |
     |  |     |   Email:    ejg_at_sequent.com                               |
     |  +-----+   Address:  25 Burlington Mall Road, Burlington, MA 01803 |
     |            Phone:    617-229-8881             FAX:    617-229-4894 |
     +====================================================================+

Received on Thu Jun 10 1993 - 17:41:41 CEST