- 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:
Databases (see CREATE DATABASE)
Schemas (see CREATE SCHEMA)
Tables (see CREATE TABLE)
External tables (see CREATE EXTERNAL TABLE)
Note
Currently, the function output for a database or schema does not include any external tables contained in the schema.
Views (see CREATE VIEW)
Streams (see CREATE STREAM)
Tasks (see CREATE TASK)
Sequences (see CREATE SEQUENCE)
File formats (see CREATE FILE FORMAT)
Pipes (see CREATE PIPE)
UDFs (see CREATE FUNCTION)
Stored Procedures (see CREATE PROCEDURE)
Policy (Column-level Security masking policies only, see: CREATE MASKING POLICY)
Syntax¶
GET_DDL( '<object_type>' , '[<namespace>.]<object_name>' [ , <use_fully_qualified_names_for_recreated_objects> ] )
Arguments¶
Required:
object_typeSpecifies the type of object for which the DDL is returned. Valid values (corresponding to the supported object types) are:
DATABASE
SCHEMA
TABLE (including for external tables)
VIEW (including for materialized views)
STREAM
TASK
SEQUENCE
FILE_FORMAT
PIPE
FUNCTION (for UDFs)
PROCEDURE (for stored procedures)
POLICY (for Column-level Security masking policies)
namespace.object_nameSpecifies 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, tasks, sequences, file formats, pipes, policies, and UDFs), takes the form of
database.schemaorschema.
Namespace is optional if a database and schema are currently in use within the user session; otherwise, it is required.
Optional:
use_fully_qualified_names_for_recreated_objectsIf
TRUE, the generated DDL statements use fully-qualified names for the objects to be recreated.Default:
FALSE.Note
This does not affect the names of other objects referenced in the DDL statement (e.g. the name of a table referenced in a view definition).
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_typeandobject_name(includingnamespaceif specified) must be enclosed in single quotes.For
object_type,TABLEandVIEWare interchangeable. If aTABLEobject 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_typeisFUNCTION(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_typeisPROCEDUREand 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 [ , ... ] ] )'.Querying this function for most Snowflake object types requires the same minimum permissions needed to view the object (using DESCRIBE <object> or SHOW <objects>). Snowflake restricts viewing special objects such as secure views to the owner (i.e. the role with the OWNERSHIP privilege on the object).
Collation Details¶
Collation information is included in the input.
Examples¶
Return the DDL used to create a view named books_view:
select get_ddl('view', 'books_view'); +-----------------------------------------------------------------------------+ | GET_DDL('VIEW', 'BOOKS_VIEW') | |-----------------------------------------------------------------------------| | | | create or replace view BOOKS_VIEW as select title, author from books_table; | | | +-----------------------------------------------------------------------------+
Return the DDL used to create a schema named books_schema and the objects in the schema (the table books_table
and the view books_view):
select get_ddl('schema', 'books_schema'); +-----------------------------------------------------------------------------+ | GET_DDL('SCHEMA', 'BOOKS_SCHEMA') | |-----------------------------------------------------------------------------| | create or replace schema BOOKS_SCHEMA; | | | | create or replace TABLE BOOKS_TABLE ( | | ID NUMBER(38,0), | | TITLE VARCHAR(255), | | AUTHOR VARCHAR(255) | | ); | | | | create or replace view BOOKS_VIEW as select title, author from books_table; | | | +-----------------------------------------------------------------------------+
Return the DDL that uses fully-qualified names for the objects to be recreated:
select get_ddl('schema', 'books_schema', true); +---------------------------------------------------------------------------------------------------+ | GET_DDL('SCHEMA', 'BOOKS_SCHEMA', TRUE) | |---------------------------------------------------------------------------------------------------| | create or replace schema BOOKS_DB.BOOKS_SCHEMA; | | | | create or replace TABLE BOOKS_DB.BOOKS_SCHEMA.BOOKS_TABLE ( | | ID NUMBER(38,0), | | TITLE VARCHAR(255), | | AUTHOR VARCHAR(255) | | ); | | | | create or replace view BOOKS_DB.BOOKS_SCHEMA.BOOKS_VIEW as select title, author from books_table; | | | +---------------------------------------------------------------------------------------------------+
Note
As demonstrated in the example above, the DDL statement doesn’t use a fully-qualified name for the table used to create the view. To resolve the name of this table, Snowflake uses the name of the database and the name of the schema for the view.
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;'' | | '; | +---------------------------------------------------+
Return the DDL to create a masking policy named employee_ssn_mask to mask social security numbers. Masked values are seen unless the user’s current role is PAYROLL.
select get_ddl('policy', 'employee_ssn_mask'); ---------------------------------------------------------------------------+ GET_DDL('POLICY', 'EMPLOYEE_SSN_MASK') | ---------------------------------------------------------------------------+ create masking policy employee_ssn_mask as (val string) returns string -> | case | when current_role() in ('PAYROLL') | then val | else '******' | end; | ---------------------------------------------------------------------------+