Re: SQL Plus Question

Mark Riehl wrote:
>
> Walter,
>
> Maybe I asked the wrong question. I did get output with dba privileges, but
> it isn't what I was looking for.
>
> 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.
>
> Thanks for the help,
> Mark
>

That is more difficult. There is no native way of doing it using sqlplus. You'd have to either write a pl/sql script that would do this for you or else you'd have to use the OS scripting. Here's how I would do quick and dirty in UNIX:

#!/bin/ksh
sqlplus -s user/pass <<-sqlEOF >tables.txt set pages 0;
set feedback off;
whenever sqlerror exit failure;
select owner||'.'||table_name
from dba_tables
order by 1;
exit success;
sqlEOF

exec 3<tables.txt
while read -u3 Buff;do

    print -u2 "Table: ${Buff}";
    sqlplus -s user/pass <<-sqlEOF
    set pages 0;
    set feedback off;
    whenever sqlerror exit failure;
    describe ${Buff};
    exit success;
    sqlEOF
done
exit 0;

However, if you are talking about a full-blown entity-relationship-diagram you will probably have to buy a third-party tool like ERWin - however, that is fairly expensive and only shows relationships when primary/foreign key constraints are defined properly. Received on Thu Jan 04 2001 - 13:42:28 CST