Since Oracle 9i, Oracle added a new utility called dbms_metadata.get_ddl. This utility provides you with object DDL generated out of Oracle Data Dictionary. It is extremely handy and useful.
For example, you can use:
set long 500000
select dbms_metadata.get_ddl(‘TABLESPACE’,’TablespaceName’) from dual;
to get a particular tablespace’s DDL. You can also use it for users, role, and other objects.
To get a table’s DDL, use:
set long 500000
select dbms_metadata.get_ddl(‘TABLE’,’TableName’,’SchemaName’) from dual;
Thanks to a fellow DBA for this great tip. You know who you are:)
2 responses to “Generate Oracle objects DDL using dbms_metadata.get_ddl”
I need to return SQL SERVER 2005 view’s metadata to my data grid. So, a function like
select dbms_metadata.get_ddl(’TABLE’,'TableName’,'SchemaName’) from dual;
really helps for me.
Does SQL Server 2005 have the similiar function?
Thanks in advanced.
regards,
Bontor
Yes, it does.
The easiest is probably through point-and-click. Go to Management Studio, go to either the database or views (I am not at a computer where I can verify at the moment), and pick the scripting tasks, and you will see the DDL for them.
If you are into scripting, it is possible to write vbscript and/or PowerShell script to do this.
Good luck!