Google Search

Thursday, October 25, 2012

Get Create Table script (or any DDL) using DBMS_METADATA

We can use DBMS_METADATA package to get the DDL Scripts for any Objects.
If we need to remove the storage clauses, below can be used.

begin
dbms_metadata.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE );
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE);
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE',TRUE);
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',FALSE);
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',false);
end;

select dbms_metadata.get_ddl('TABLE','TABLE_NAME','SCHEMA') FROM DUAL;

And all related Indexes script can be obtained by using below query-

select dbms_metadata.get_dependent_ddl('INDEX','TABLE_NAME','SCHEMA') from dual;

No comments:

Post a Comment