Displaying Table DDL- Tables and Constraints

Sometimes, a table definition is not documented at the time of creation, maybe from a DBA or developer or directly from the application. Normally, you should maintain the database DDL code in a source control repository or in some sort of modeling tool.

If your shop does not have the DDL source code, there are a few ways that you can manually reproduce DDL:
• Query the data dictionary.
• Use Data Pump.
• Use the DBMS_METADATA package.
• Use data tools such as SQL Developer.

The Data Pump utility is an excellent method for generating the DDL used to create database objects. Using Data Pump to generate DDL is covered in detail in Chapter 13.
The GET_DDL function of the DBMS_METADATA package is usually the quickest way to display the DDL required to create an object. This example shows how to generate the DDL for a table named INV:
SQL> set long 10000
SQL> select dbms_metadata.get_ddl(‘TABLE’,’INV’) from dual;

Here is some sample output:

DBMS_METADATA.GET_DDL(‘TABLE’,’INV’) ————————————-SQL> CREATE TABLE “MV_MAINT”.”INV”
( “INV_ID” NUMBER,
“INV_DESC” VARCHAR2(30 CHAR), “INV_COUNT” NUMBER
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE “USERS”;

The following SQL statement displays all the DDL for the tables in a schema:

SQL> select dbms_metadata.get_ddl(‘TABLE’,table_name) from user_tables;

If you want to display the DDL for a table owned by another user, add the SCHEMA parameter to the GET_DDL procedure:

SQL> select
dbms_metadata.get_ddl(object_type=>’TABLE’, name=>’INV’, schema=>’INV_APP’) from dual;

Note You can display the DDL for almost any database object type, such as INDEX, FUNCTION, ROLE, PACKAGE, MATERIALIZED VIEW, PROFILE, CONSTRAINT, SEQUENCE, and SYNONYM.