Categories:
Utility & Hash Functions (Utility)

GET_DDL

Returns a DDL statement that can be used to recreate the specified object. For databases and schemas, GET_DDL is recursive, i.e. it returns the DDL statements for recreating all supported objects within the specified database/schema.

GET_DDL currently supports the following object types:

Syntax

GET_DDL( '<object_type>' , '[<namespace>.]<object_name>' )

Arguments

object_type

Specifies the type of object for which the DDL is returned. Valid values (corresponding to the supported object types) are:

  • DATABASE
  • SCHEMA
  • TABLE
  • VIEW (including for materialized views)
  • SEQUENCE
  • FILE_FORMAT
  • FUNCTION (for UDFs)
namespace.object_name

Specifies the fully-qualified name of the object for which the DDL is returned.

Namespace is the database and/or schema in which the object resides:

  • Not used for databases.
  • For schemas, takes the form of database.
  • For schema objects (tables, views, sequences, file formats, and UDFs), takes the form of database.schema or schema.

Namespace is optional if a database and schema are currently in use within the user session; otherwise, it is required.

Usage Notes

  • object_type and object_name (including namespace if specified) must be enclosed in single quotes.
  • For object_type, TABLE and VIEW are interchangeable. If a TABLE object type is specified, and the object specified by name is a view, the function returns the DDL for the view and vice-versa.
  • If object_type is FUNCTION (i.e. UDF) and the UDF has arguments, you must include the argument data types as part of the function name, in the form of 'function_name( [ arg_data_type [ , ... ] ] )'.

Examples

Return the DDL used to create a view named tpch_customer:

select get_ddl('view', 'tpch_customer');

----------------------------------------------------+
        GET_DDL('VIEW', 'TPCH_CUSTOMER')            |
----------------------------------------------------+
 create  or replace view tpch_customer as select    |
     customer:c_custkey::integer c_custkey,         |
     customer:c_name::string c_name,                |
     customer:c_address::string c_address,          |
     customer:c_nationkey::integer c_nationkey,     |
     customer:c_phone::string c_phone,              |
     to_decimal(customer:c_acctbal,12,2) c_acctbal, |
     customer:c_mktsegment::string c_mktsegment,    |
     customer:c_comment::string c_comment           |
 from jcustomer                                     |
----------------------------------------------------+

Return the DDL used to create a schema named seqschema, which contains table t1 and sequence seq:

select get_ddl('schema', 'seqschema');

-------------------------------------------------------------+
              GET_DDL('SCHEMA', 'SEQSCHEMA')                 |
-------------------------------------------------------------+
 create or replace schema SEQSCHEMA;                         |
 create or replace table T1(                                 |
 N NUMBER(38,0) NOT NULL                                     |
 );                                                          |
 create or replace sequence SEQ start with 2 increment by 2; |
-------------------------------------------------------------+

Return the DDL used to create a UDF named multiply that has two parameters of type NUMBER:

select get_ddl('function', 'multiply(number, number)');

--------------------------------------------------+
 GET_DDL('FUNCTION', 'MULTIPLY(NUMBER, NUMBER)')  |
--------------------------------------------------+
 CREATE OR REPLACE "MULTIPLY"(A NUMBER, B NUMBER) |
 RETURNS NUMBER(38,0)                             |
 COMMENT='multiply two numbers'                   |
 AS 'a * b';                                      |
--------------------------------------------------+