Re: newbie dumping tables and columns to text file

On Feb 9, 6:10 am, "pankaj_wolfhun..._at_yahoo.co.in" <pankaj_wolfhun..._at_yahoo.co.in> wrote:
> On Feb 9, 1:39 pm, seannakas..._at_yahoo.com wrote:
>
>
>
>
>
> > Hello, I'm a newbie to sql and oracle. I'm using sqlplus on windows
> > (probably doesn't matter what platform) and want to dump out all the
> > user tables and columns to a text file. I would like the format to be
> > as follows:
>
> > MyTable1 > MyColumn1
> > MyTable1 > MyColumn2
> > ...
> > MyTable2 > MyColumn1
> > MyTable2 > MyColumn2
> > ...
>
> > Is there an sql script that can do this?
>
> > thanks,
> > Sean.
>
> (1) create an script (say script_test.sql on D:\ ) as
>
> set heading off
> set feedback off
> set echo off
> spool d:\table.sql
> SELECT TABLE_NAME||' > '||COLUMN_NAME FROM USER_TAB_COLUMNS
> ORDER BY TABLE_NAME, COLUMN_NAME;
> spool off;
>
> (2) run the script on sql*plus as @d:\script_test.sql
>
> This will create a output file on D:\ named as table.sql with the
> desired output.- Hide quoted text -
>
> - Show quoted text -

A couple more SQLPlus formatting commands that might be of use depending on the number of columns and length of the columns to be output in one line.

set linesize nnn -- if the line is longer than the default set trimspool on -- to eliminate trailing blanks from spool file lines

See the SQLPlus Users Guide and Reference

HTH -- Mark D Powell -- Received on Fri Feb 09 2007 - 07:46:00 CST