Re: SQL Plus Question

From: Happl Oberlin <happl_at_mail.com>
Date: Fri, 05 Jan 2001 07:44:04 +0100
Message-ID: <happl-65952F.07440405012001@news.cis.dfn.de>

In article <N7456.99785$w35.17370271_at_news1.rdc1.nj.home.com>, "Mark Riehl" <mriehl_at_home.com> wrote:

>What's the easiest way to display the structure (column headings would be
>fine) of all the tables in a database (if I don't know their names)? For
>example, assume you were just handed a new database and you want to list
>the tables and the format of each table in the database.

The following will create a list with all tables and columns:

set echo off verify off feedback off termout on set pagesize 45 newpage 0 linesize 120

column sysdatum              format a8           noprint new_val tdatum
column table_name            format a30          heading Tabellen-Name
column column_id             format 999          heading Seq
column column_name           format a30          heading Feld-Name
column data_type             format a8           heading Daten-Typ
column laenge                format a6           heading Laenge
column oblig                 format a8           heading Oblig

break on table_name skip 1
spool tabcols
select tab.table_name, tabcol.column_id, tabcol.column_name,

      data_type,
      lpad(decode(data_precision,null,data_length,data_precision),3,' ')
      ||
      rpad(decode(data_scale,null,'   ','.'||data_scale),3,' ') length,
      decode(nullable,'Y','NULL','N','NOT NULL') oblig

from user_tables tab, user_tab_columns tabcol where tab.table_name = tabcol.table_name order by tab.table_name, tabcol.column_id /
spool off

It reads USER_TABLES and USER_TAB_COLUMNS but you can change that to DBA_TABLES and DBA_TAB_COLUMNS.

Greetings from Switzerland -- Grüsse aus der Schweiz Happl Received on Fri Jan 05 2001 - 00:44:04 CST