2

I have so far figured out that to describe a table I can use the below:

select dbms_metadata.get_ddl('TABLE','<my table name>','<table owner>') from dual;

I also found that I can get a list of tables from the current user using the below statement:

select table_name from user_tables;

However I need to find a way to combine these two so I get a (preferably SQL file) output which basically describes all the tables in the current schema. How can I go about that?

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Prasanna Narayanan
  • 437
  • 1
  • 3
  • 13
  • if you want to do it programmatically - just run the first query in a loopu. otherwise, get any Oracle IDE (Oracle SQL developer, for instance) - they all have some sort of schema export utilities. – Kirill Leontev Dec 05 '19 at 16:55

1 Answers1

5

Call dbms_metadata in your query on user_tables:

select dbms_metadata.get_ddl('TABLE',table_name,user) 
from   user_tables;
Chris Saxon
  • 9,105
  • 1
  • 26
  • 42