Search code examples
sqloracle-databaseplsqlsql-scriptsdata-dictionary

How do I write the table structure in Oracle from an existing schema?

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?

Solution

  • Call dbms_metadata in your query on user_tables:

    select dbms_metadata.get_ddl('TABLE',table_name,user) 
from   user_tables;