CREATE TABLE reverse engineering in Oracle

Question

In Oracle SQL Developer, there's a "SQL" tab for each table. This tab contains most of the SQL code (CREATE TABLE, CREATE TRIGGER, etc) that's needed to recreate the table.

Is this information available programatically from the database system, or is this an application feature of SQL Developer? If the former, what commands/statements would I need to run to retrieve this information? If the later, are there any clever ways to get SQL Developer to export these statements?

Answer 1

There are lots of information, but here are the main queries:

SELECT  *
FROM    dba_tables

SELECT  *
FROM    dba_tab_columns

SELECT  *
FROM    dba_ind_columns

To see what SQL Developer actually outputs, enable trace for all sessions with a LOGON TRIGGER and look into the trace file created by SQL Developer's internal session.

Answer 2

If you are using Oracle 9i+ then you are looking for the DBMS_METADATA package. http://download.oracle.com/docs/cd/B10500_01/appdev.920/a96612/d_metada.htm. It will allow you to extract whatever DDL you want.

If you are looking for more specific information there is a whole host of views you can access for specific data elements similar to the ones given by @Quassnoi.

Answer 3

You are looking for the DDL of your database objects.

You can use the Oracle bundled DBMS_METADATA package to get it, from any PL/SQL prompt, with the GET_DDL function.

Answer 4

I use TOAD vs Oracle SQL Developer.

When I click on a "Script" tab when viewing an object (like a table) TOAD executes a whole host of queries and then compiles the "script" from the output of all of these queries.

dba_tables dba_tab_columns dba_ind_columns

...

I think replicating this functionality would be a tedious task.