Table names, and loop to describe

Question

Working in Oracle 10g. Easy way to list all tables names (select table_name from dba_tables 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?

Answer 1

You could query against DBA_TAB_COLUMNS (or USER_TAB_COLUMNS).

Nicolas.

Answer 2

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!*/

Answer 3

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

Answer 4

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;