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)

  • STREAM

  • SEQUENCE

  • FILE_FORMAT

  • PIPE

  • FUNCTION (for UDFs)

  • PROCEDURE (for stored procedures)

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, streams, sequences, file formats, pipes, 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.

Returns

Returns a string (VARCHAR) containing the text of the DDL statement that created the object.

For UDFs and stored procedures, the output might be slightly different from the original DDL. For example, if the UDF or stored procedure contains JavaScript code, the delimiter characters around the JavaScript code might be different. Also, if the original CREATE PROCEDURE statement did not specify EXECUTE AS OWNER or EXECUTE AS CALLER, the output from GET_DDL include EXECUTE AS OWNER because that is the default.

Usage Notes

  • Currently, GET_DDL does not support external tables.

  • 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 [ , ... ] ] )'.

  • If object_type is PROCEDURE and the stored procedure has arguments, then you must include the argument data types as part of the function name, in the form of 'procedure_name( [ arg_data_type [ , ... ] ] )'.

Collation Details

  • Collation information is included in the input.

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';                                      |
--------------------------------------------------+

Return the DDL to create a stored procedure named stproc_1 that has one parameter of type FLOAT:

SELECT GET_DDL('procedure', 'stproc_1(float)');
+-------------------------------------------------+
| GET_DDL('PROCEDURE', 'STPROC_1(FLOAT)')         |
|-------------------------------------------------|
| CREATE OR REPLACE PROCEDURE "STPROC_1"(F FLOAT) |
| RETURNS FLOAT                                   |
| LANGUAGE JAVASCRIPT                             |
| EXECUTE AS OWNER                                |
| AS '                                            |
| ''return F;''                                   |
| ';                                              |
+-------------------------------------------------+