Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2003
    Posts
    24

    Unanswered: Another 'noob'ish question

    I know how to get a single table description using:

    desc my_table_name


    But I need to get the descriptions for 200+ tables with names ending in _SP and really don't want to have to type them out individually.

    Any suggestions?

  2. #2
    Join Date
    Apr 2001
    Location
    Netherlands
    Posts
    191
    You can select all you need to know from user_tab_columns

    this has the table name, column names, datatype etcetera.

    (also contains views)
    Ruud Schilders
    -----------------
    Oracle DBA
    e-mail : ruud@schilders.it
    URL : www.schilders.it
    Twitter : www.twitter.com/ruudschilders

  3. #3
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow TABLE_NAMES

    I can give u a starting point to this.

    select table_name from user_tables where table_name like '%_SP';
    This would give you all the table names ending with _SP.

    Also, you could use PLSQL procedure to run a loop looking at all table_names and doing a desc on the iterated table names.

    Regards
    Aruneesh

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Let SQLPlus write it for you....

    spool c:\...\filename.sql

    select 'desc '||table_name||';' from user_tables where table_name like '%_sp';

    spool off

    @c:\...\filename.sql

    Gregg

  5. #5
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    semi colon doesnt work

    Graham,
    The semi colon in the table_name which is being created doesnt seem to work like expected.

    select 'desc '||table_name||';' from user_tables where table_name like '%_sp';
    Aruneesh

  6. #6
    Join Date
    Apr 2001
    Location
    Netherlands
    Posts
    191
    the ; is not needed with describe.

    select 'desc '||table_name from user_tables where table_name like '%_sp';

    still think my method is best .. of course
    Last edited by Ruudboy; 07-16-03 at 15:41.
    Ruud Schilders
    -----------------
    Oracle DBA
    e-mail : ruud@schilders.it
    URL : www.schilders.it
    Twitter : www.twitter.com/ruudschilders

  7. #7
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Unique solution

    Few months back I had a similar situation while creating a data dictionary just using the SQLs.
    If you are just using Oracle as standalong, the above solution provides the best solution possible I have seen.
    Else you could make use of MetaData.

    Thanx and Regards
    Aruneesh

  8. #8
    Join Date
    Jul 2003
    Posts
    24
    Didn't quite work Gregg [didn't get table names].....like the thought though and snagged your code to tweak later.....thanks

    Thanks also to the RudeDude......just what I needed

  9. #9
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Alright ... try

    select 'desc '||table_name from user_tables
    where table_name like '%_SP';


    Gregg

Posting Permissions

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