Query to get table object DDL with_table_index

 

Query to get  table object DDL with_table_index

       
---Table DDL
select dbms_metadata.get_ddl('TABLE','GRPMST','MFTRAN') from dual;
---index Name
select INDEX_NAME, TABLE_OWNER, TABLE_NAME, UNIQUENESS from ALL_INDEXES;
--Index DDL
select dbms_metadata.get_ddl('INDEX','SYS_C0014037','username') from dual;

----Materialized_view DDL

select dbms_metadata.get_ddl( 'MATERIALIZED_VIEW', '&Mview_name','&onwer') from dual


--ALL DETAIL
select DBMS_METADATA.GET_DDL('TABLE',OBJECT_NAME,OWNER) FROM Dba_objects where owner = 'ADMINDBA' and object_name = 'SYS_EXPORT_FULL_11'
and object_type = 'TABLE'
union all
select dbms_metadata.GET_DEPENDENT_DDL ('COMMENT', TABLE_NAME, OWNER ) FROM (select table_name,owner from Dba_col_comments where owner = 'ADMINDBA' and table_name = 'SYS_EXPORT_FULL_11' and comments is not null
 union 
select table_name,owner from sys.Dba_TAB_comments where owner = 'ADMINDBA' and table_name = 'SYS_EXPORT_FULL_11' and comments is not null) 
union all 
select DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME, OWNER) FROM        (select index_name, owner from sys.Dba_indexes where table_owner = 'ADMINDBA' and table_name = 'SYS_EXPORT_FULL_11'
 and generated = 'N'   
 minus 
 select index_name, owner from sys.Dba_constraints where owner = 'ADMINDBA' and table_name = 'SYS_EXPORT_FULL_11')    
 union all       
 select dbms_metadata.GET_DDL ('TRIGGER', trigger_name ,owner ) from Dba_triggers where table_owner = 'ADMINDBA' and table_name = 'SYS_EXPORT_FULL_11'