Table DDL Generation (qtableddl.sql)

What This Script Does

This script generates complete DDL (Data Definition Language) statements for tables using Oracle’s DBMS_METADATA package. It extracts table definitions with constraints formatted as separate ALTER statements and excludes storage clauses for cleaner output. Essential for recreating tables or documenting database schemas.

The Script

set heading off
set pagesize 0
set long 100000

rem
ttitle 'Get table DDL'
rem

rem
define Owner='&owner'
define Table='&table'
rem

begin
  dbms_metadata.set_transform_param
            ( DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', true );
            dbms_metadata.set_transform_param
            ( DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', false );
            dbms_metadata.set_transform_param
            ( DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE );
end;
/
column x format a80 WORD_WRAPPED

            select dbms_metadata.get_ddl('TABLE',u.table_name,u.owner) x from
dba_tables u
where
u.nested = 'NO'
and table_name like nvl(upper('&table'),'%')
and owner  like nvl(upper('&owner'),'%')
and (u.iot_type is NULL or u.iot_type='IOT');
rem
undefine Owner
undefine Table

Parameters

The script prompts for:

owner: Schema owner name (optional, defaults to all owners)
table: Table name pattern (optional, defaults to all tables)

Usage

-- Basic usage (will prompt for parameters)
@qtableddl.sql

-- Example values when prompted:
-- owner: SCOTT
-- table: EMP%

Sample Output

                               Get table DDL

CREATE TABLE "SCOTT"."EMPLOYEES"
   (  "EMPLOYEE_ID" NUMBER(6,0),
  "FIRST_NAME" VARCHAR2(20 BYTE),
  "LAST_NAME" VARCHAR2(25 BYTE) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE,
  "EMAIL" VARCHAR2(25 BYTE) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE,
  "PHONE_NUMBER" VARCHAR2(20 BYTE),
  "HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE,
  "JOB_ID" VARCHAR2(10 BYTE) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE,
  "SALARY" NUMBER(8,2),
  "COMMISSION_PCT" NUMBER(2,2),
  "MANAGER_ID" NUMBER(6,0),
  "DEPARTMENT_ID" NUMBER(4,0)
   );

ALTER TABLE "SCOTT"."EMPLOYEES" ADD CONSTRAINT "EMP_EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID");
ALTER TABLE "SCOTT"."EMPLOYEES" ADD CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID") REFERENCES "SCOTT"."DEPARTMENTS" ("DEPARTMENT_ID");
ALTER TABLE "SCOTT"."EMPLOYEES" ADD CONSTRAINT "EMP_MANAGER_FK" FOREIGN KEY ("MANAGER_ID") REFERENCES "SCOTT"."EMPLOYEES" ("EMPLOYEE_ID");

Features

Clean Output: Storage clauses are excluded for cleaner DDL
Separate Constraints: Constraints are formatted as ALTER statements
Flexible Filtering: Pattern matching for owner and table names
IOT Support: Includes Index-Organized Tables
SQL Terminators: Statements end with semicolons for easy execution