Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2003
    Location
    Toronto, Ontario, Canada
    Posts
    34

    Question Unanswered: spooling tables in a shema to the file

    Hi

    What I am trying to do is have a text file with the description of all tables in my database. Here is what I have so far

    spool data.sql
    select 'desc'|| ' ' ||table_name|| ' ' ||';' from all_tables;
    spool off
    spool data.txt
    @data
    spool off

    what happens is I do get the description of the tables which is what I want but it doesn't give me the table names

    For eg.

    Name Null? Type
    ------------------------------------
    abc notnull number
    efg varchar2(30)


    Name Null? Type
    ------------------------------------
    hij notnull number
    klm date


    as you can see this can get very confusing as to which table is which.

    Does anyone know a solution to this problem?

    Thanks

    Hamza

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Hally,

    it may not quite be the answer you're after, but why not query the columns directly rather than using DESC?

    This also gives you the flexibility to list table comments, column comments and if you wish to add in referential constraints etc.

    Hth
    Bill

    select t.table_name,
    c.column_name,
    c.data_type,
    c.data_length,
    c.data_precision,
    c.data_scale,
    c.nullable
    from user_tables t,
    user_tab_columns c
    where c.table_name = t.table_name
    order by t.table_name, c.column_id

  3. #3
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    ... but this might be closer to what you're after...

    spool data.sql
    select 'select '''||table_name||''' from dual;'||chr(13)||chr(10)||'desc'|| ' ' ||table_name|| ' ' ||';' from user_tables;
    spool off
    spool data.txt
    @data
    spool off

  4. #4
    Join Date
    Feb 2003
    Location
    Toronto, Ontario, Canada
    Posts
    34
    Thanks for your reply, I was actually doing this task to give someone a list of our tables. The person I was giving the list to only wanted Table Name, Columns in the tables, whether the columns were nullable, and the data types of the columns which is why I thought desc would be perfect. I actually figured out what my problem was. I was using sqlplus to spool this file and the echo was set to off. Here is my revised query:

    set feedback off
    set heading off
    set echo off

    spool data.sql
    select 'desc'|| ' ' ||table_name||';' from user_tables;
    spool off

    set echo on
    set heading on

    spool data.txt
    @data
    spool off


    Originally posted by billm
    Hi Hally,

    it may not quite be the answer you're after, but why not query the columns directly rather than using DESC?

    This also gives you the flexibility to list table comments, column comments and if you wish to add in referential constraints etc.

    Hth
    Bill

    select t.table_name,
    c.column_name,
    c.data_type,
    c.data_length,
    c.data_precision,
    c.data_scale,
    c.nullable
    from user_tables t,
    user_tab_columns c
    where c.table_name = t.table_name
    order by t.table_name, c.column_id

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •