Table names, and loop to describe

Working in Oracle 10g. Easy way to list all tables names (select table_name from dba_ta开发者_运维技巧bles where owner = 'me') But now that I have the table names, is there an easy way to loop through them and do a 'describe' on each one in sequence?

---

You could query against DBA_TAB_COLUMNS (or USER_TAB_COLUMNS).

Nicolas.

---

Not sure you can do a describe from within PL/SQL. I just tried using execute immediate 'describe some_table', that doesn't work either. Your next choice would be to query DBA_TAB_COLUMNS, or create a new file with all your describe statements (using dbms_output from pl/sql and spool to create the file) and then execute that file. Maybe like this:

spool temp_file.sql
BEGIN
    /*or you could have a loop here with as many put_lines as you need, it will all end up in the new script file.*/
    dbms_output.put_line('describe some_table');
END;
/
spool off
@temp_file.sql
/*I have not actually tried running this code, beware syntax errors!*/

---

You can do this in PL/SQL using DBMS_METADATA.GET_DDL, e.g. (example taken from docs):

SET LONG 2000000
SET PAGESIZE 0
SELECT DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT') FROM DUAL;

---

1. login with the user and then run the following commands, first file will contain the describe commands and the second file will be the desired file containing all the descriptions of all tables for logged in user

   spool desctables.sql
   select 'describe '||table_name||';' from user_tables;
   spool off
   spool alltables.txt
   @desctables.sql
   spool off
   

---

I'd recommend querying dba_tab_columns, as N. Gasparotto suggested, but if you really want describe output then create a file mulit-describe.sql with the following:

set pagesize 0
set termout off
set feedback off
set verify off

spool mdtmp.sql
select 'describe ' || owner || '.' || table_name
from dba_tables
where OWNER = upper('&1')
/

spool off
set termout on
@mdtmp.sql

Within SQL*PLUS run by:

@multi-describe ME