Skip to main content

DDL File

Create a PawSQL Workspace from a DDL file.

Dump a DDL file​

MySQL/MariaDB​

For MySQL and MariaDB, you can use mysqldump utility to dump specified tables or a whole database definition to a sql file.

  • Synopsis

    mysqldump [options] db_name [tbl_name ...]

  • dump database objects in database tpch

    mysqldump -uroot -pmysql -d tpch >\tmp\tpch.sql

  • dump specified table customer definition in database tpch

    mysqldump -uroot -pmysql -d tpch customer> \tmp\tpch.customer.sql

You should specify -d or --no-data in the command, to dump the create table statements without data.

PostgreSQL​

For PostgreSQL, you can use pg_dump utility to dump specified tables or a whole database definition to a sql file.

  • Synopsis

    pg_dump [connection-option...] [option...] [dbname]

  • dump database objects in database tpch

    pg_dump -h localhost -p 5432 -U postgres -s tpch >\tmp\tpch.sql

  • dump specified table customer definition in database tpch

    pg_dump -h localhost -p 5432 -U postgres -s -t customer tpch > customer.sql

You should specify -s or --schema-only in the command, to dump the create table statements without data.

Opengauss​

For Opengauss, you can use gs_dump utility to dump specified tables or a whole database definition to a sql file.

  • Synopsis

    gs_dump [connection-option...] [option...] [dbname]

  • dump database objects in database tpch

    gs_dump -h localhost -p 5432 -U postgres -s tpch >\tmp\tpch.sql

  • dump specified table customer definition in database tpch

    gs_dump -h localhost -p 5432 -U postgres -s -t customer tpch > customer.sql

You should specify -s or --schema-only in the command, to dump the create table statements without data.

How to use Oracle's SPOOL command line tool and DBMS_METADATA.GET_DDL function to export the table structure​

Oracle​

In Oracle database, there is no command-line tool that directly exports DDL statements to a .sql file like MySQL's mysqldump command. However, you can use the SPOOL command-line tool and the DBMS_METADATA.GET_DDL function in Oracle to export table structures.

  • Command syntax

       SPOOL syntax:
           SPOOL [file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]] | OFF | OUT]

       DBMS_METADATA.GET_DDL syntax:
           DBMS_METADATA.GET_DDL (
               object_type     IN VARCHAR2,
               name            IN VARCHAR2,
               schema          IN VARCHAR2 DEFAULT NULL,
               version         IN VARCHAR2 DEFAULT 'COMPATIBLE',
               model           IN VARCHAR2 DEFAULT 'ORACLE',
               transform       IN VARCHAR2 DEFAULT 'DDL')
           RETURN CLOB;

  • Export the DDL statements for all tables in the tpch database:

        SELECT DBMS_METADATA.GET_DDL('TABLE', table_name, owner) || ';' 
        FROM all_tables
        WHERE owner = 'TPCH';

  • Export the DDL statement for the table customer in the tpch database:

       SELECT dbms_metadata.get_ddl('TABLE', 'CUSTOMRE', 'TPCH) || ';' FROM dual;

To export table structures, you need to use the file_name parameter in the SPOOL command to specify the output file name and the OFF flag to end the output. To retrieve the table structure, you need to use the object_type object type, name object name, and schema schema name parameters in the DBMS_METADATA.GET_DDL function.

Steps to follow​

  1. Open a text editor and create a file named export_ddl.sql.
  2. In the export_ddl.sql file, write the following content: 
     SET PAGESIZE 0
     SET LINESIZE 1000
     SET LONG 1000000
     SET TRIMSPOOL ON
     SET TRIMOUT ON

     SPOOL ddl_export.sql

     SELECT DBMS_METADATA.GET_DDL('TABLE', table_name, owner) || ';'
     FROM all_tables
     WHERE owner = 'YOUR_SCHEMA_NAME';

     SPOOL OFF
    Replace YOUR_SCHEMA_NAME with the name of the schema for which you want to export DDL statements.
  3. Save and close the export_ddl.sql file.
  4. Open a command-line interface and log in to the Oracle database using SQL*Plus.
  5. In the command-line, run the following command: 
    SQL> @path_to_export_ddl.sql
    ```
    Replace path_to_export_ddl.sql with the full path of the export_ddl.sql file.
  6. After running the above command, a .sql file named ddl_export.sql will be generated, which contains the DDL statements for all tables in the specified schema.

References​

The mysqldump,pg_dump and gs_dump utilities perform logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data.