Generating lots of DDL with DBMS_METADATA...

After using many different tools to generate DDL I usually end up back at DBMS_METADATA. DBMS_METADATA is an Oracle supplied package that became available with Oracle 9.

DBMS_METADATA is an API that can be used to generate the DDL of most any object in an Oracle database.

It is most handy for getting Table or Index DDL but there is a complete list of objects that DBMS_METADATA handles here.

Table Example
Log into a schema and run the following to generate DDL for all indexes in the schema:

set pagesize 0
set long 2000000000
spool CreateTables.sql
SELECT dbms_metadata.get_ddl('TABLE',table_name) || ';' 
FROM USER_TABLES 
ORDER BY TABLE_NAME;
spool off

Indexes example
Log into a schema and run the following to generate DDL for all indexes in the schema:

set pagesize 0
set long 2000000000
spool CreateIndexes.sql
SELECT dbms_metadata.get_ddl('INDEX',index_name) || ';' 
FROM user_indexes 
ORDER BY TABLE_NAME, INDEX_NAME;
spool off

What's Next?

DBMS_METADATA also has a transformation interface so DDL can be massaged before it is generated. Example transforms include turning on/off the storage clause, tablespace clause, and other

DBMS_METADATA also has helper functions that allow you to generate DDL of dependent objects and grants. See documentation for DBMS_METADATA.GET_DEPENDENT_DDL and DBMS_METADATA.GET_GRANTED_DDL.

-- Dave