Syntax¶

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

Arguments¶

Required:

'object_type'

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

• DATABASE

• DYNAMIC_TABLE

• EVENT_TABLE

• FILE_FORMAT

• FUNCTION (for UDFs, including data metric functions and external functions)

• ICEBERG_TABLE

• INTEGRATION (storage)

• PIPE

• POLICY (aggregation, authentication, join, masking, password, projection, row access, and session policies)

• PROCEDURE (for stored procedures)

• SCHEMA

• SEMANTIC VIEW

• SEQUENCE

• STREAM

• TABLE (for tables, external tables, and hybrid tables)

• TAG (object tagging)

• TASK

• VIEW (for views and materialized views)

• WAREHOUSE

'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 ofdatabase.

• For schema objects (tables, views, streams, tasks, sequences, file formats, pipes, policies, and UDFs), takes the form ofdatabase.schema orschema.

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

Optional:

use_fully_qualified_names_for_recreated_objects

If 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 ina view definition).

Returns¶

Returns a string (a VARCHAR value) 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 storedprocedure contains JavaScript code, the delimiter characters around the JavaScript code might be different.

In addition, note that the DDL statement returned by the function might include default values for properties. For example, evenif the original CREATE PROCEDURE statement did not specify EXECUTE AS OWNER, the DDL statement returned by the function includesEXECUTE AS OWNER, which is the default.

Usage notes¶

The following notes apply to all supported objects:

• object_type andobject_name (includingnamespace if specified) must be enclosed in single quotes.

• Forobject_type,TABLE andVIEW are interchangeable. If aTABLE object type is specified, and the object specified by name is a view, the function returns the DDL forthe view and vice-versa.

• Ifobject_type isFUNCTION (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[,...]])', wherefunction_name is the name of the function andarg_data_type is the data type of the argument.

• Ifobject_type isPROCEDURE and the stored procedure has arguments, then you must include theargument data types as part of the function name, in the form of'procedure_name([arg_data_type[,...]])'.

• For Iceberg tables:

  • If you specify aTABLE object that’s an Iceberg table, the function returns the DDL for the Iceberg table.

  • IfBASE_LOCATION was specified in the original CREATE ICEBERG TABLE statement,the function returns the original user input. Otherwise,the function returns the Snowflake-constructed file path (including the random 8-character string).For more information, seeData and metadata directories.

• Querying this function for most Snowflake object types requires the same minimum permissions needed to view the object (usingDESCRIBE <object> orSHOW <objects>). Snowflake restricts viewing special objects such as secure views to the owner (i.e. the role with the OWNERSHIP privilege on the object).

The following notes are specific to view objects. The query result always:

• Returns lowercase SQL text forcreateorreplaceview, even if the casing in the original SQL statement used to create theview was uppercase or mixed case.

• Includes the OR REPLACE clause.

• Includes the SECURE property, if the view is secure.

• Excludes the COPY GRANTS view parameter, even if the original CREATE VIEW statement specifies the COPY GRANTS parameter.

• Generates the column list.

  If a masking policy is set on a column, the result specifies the masking policy for the column.

• Removes in-line SQL comments before the view body (that is, before AS). For example, in the following code, the commentimmediately prior to the AS clause is removed:

  CREATEVIEWview_t1-- GET_DDL() removes this comment.ASSELECT*FROMt1;

  Copy

The following notes apply specifically to table and view objects with a tag or policy:

• The role executing the GET_DDL query must have the global APPLY MASKING POLICY, APPLY ROW ACCESS POLICY, APPLY AGGREGATION POLICY, APPLY JOIN POLICY,APPLY PROJECTION POLICY, or APPLY TAG privilege and the USAGE privilege on the database and schema containing the policy or tag.Otherwise, Snowflake replaces the policy with#UNKNOWN_POLICY and the tag with#UNKNOWN_TAG='#UNKNOWN_VALUE. This textindicates that the column or the object is protected by a policy and a tag is set on the object or column. If this text is not removedprior to recreating the object, the CREATE OR REPLACE<object> statement fails.

  If this text is present in the GET_DDL query result, prior to recreating the object, consult with your internal governance administratorto determine which policies and tags are necessary for the columns or object. Finally, edit the GET_DDL query result and then recreatethe object.

  Without the mentioned privileges, this table function does not return the corresponding row for the policy and tag assignments in theoutput of calling the function.

• When multiple tags are set on the object or column, the GET_DDL output sorts the tags alphabetically by tag name.

• Dropping a tag removes the tag from the GET_DDL output.

• If a tag is set on the table or view, the GET_DDL output for the table or view includes the tag assignments in the CREATE OR REPLACEstatement.

• If a masking policy is set on the column or a row access policy is set on the table, the GET_DDL output includes the policy assignmentsusing the WITH keyword.

When a tag is set on the database or the schema, the GET_DDL output includes:

• An ALTER DATABASE statement when the tag is set on the database.

• An ALTER DATABASE statement and an ALTER SCHEMA statement when the tag is set on both the database and schema.

• An ALTER SCHEMA statement when the tag is set on the schema.

• A CREATE OR REPLACE statement to generate the tag, if the tag exists in the database or schema.

The following apply to storage integrations:

• The command always returns the CREATE OR REPLACE STORAGE INTEGRATION syntax.

• If a STORAGE_AWS_EXTERNAL_ID was not specified during storage integration creation, this command returns the ID that was automaticallygenerated during storage integration creation.

Collation details¶

• Collation information is included in the input.

Examples¶

The following examples demonstrate how to use this function to retrieve the DDL statement for an object:

• Views

• Semantic views

• Schemas

• UDFs and stored procedures

• Masking policies

• Storage integrations

• Warehouses

• Hybrid tables

SELECTGET_DDL('VIEW','books_view');+-----------------------------------------------------------------------------+| GET_DDL('VIEW', 'BOOKS_VIEW')                                               ||-----------------------------------------------------------------------------||                                                                             || CREATE OR REPLACE VIEW BOOKS_VIEW as select title, author from books_table; ||                                                                             |+-----------------------------------------------------------------------------+

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

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

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

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

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

SELECTGET_DDL('INTEGRATION',s3_int);+----------------------------------------------------------------------------+| GET_DDL('INTEGRATION', 's3_int')                                           ||----------------------------------------------------------------------------|| CREATE OR REPLACE STORAGE INTEGRATION s3_int                               ||   TYPE = EXTERNAL_STAGE                                                    ||   STORAGE_PROVIDER = 'S3'                                                  ||   STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::001234567890:role/myrole'           ||   STORAGE_AWS_EXTERNAL_ID='ACCOUNT_SFCRole=2_kztjogs3W9S18I+iWapHpIz/wq4=' ||   ENABLED = TRUE                                                           ||   STORAGE_ALLOWED_LOCATIONS = ('s3://mybucket1/path1/');                   |+----------------------------------------------------------------------------+

CREATEORREPLACEWAREHOUSEmy_whWAREHOUSE_SIZE=LARGEINITIALLY_SUSPENDED=TRUE;

SELECTGET_DDL('WAREHOUSE','my_wh');

+-------------------------------------------+| GET_DDL('WAREHOUSE', 'MY_WH')             ||-------------------------------------------|| create or replace warehouse MY_WH         || with                                      ||     warehouse_type='STANDARD'             ||     warehouse_size='Large'                ||     max_cluster_count=1                   ||     min_cluster_count=1                   ||     scaling_policy=STANDARD               ||     auto_suspend=600                      ||     auto_resume=TRUE                      ||     initially_suspended=TRUE              ||     enable_query_acceleration=FALSE       ||     query_acceleration_max_scale_factor=8 ||     max_concurrency_level=8               ||     statement_queued_timeout_in_seconds=0 ||     statement_timeout_in_seconds=172800   || ;                                         |+-------------------------------------------+

CREATEORREPLACEHYBRIDTABLEht_weather(idINTPRIMARY KEY,start_timeTIMESTAMP,precipNUMBER(3,2),cityVARCHAR(20),countyVARCHAR(20));

SELECTGET_DDL('TABLE','ht_weather');

+---------------------------------------------+| GET_DDL('TABLE','HT_WEATHER')               ||---------------------------------------------|| create or replace HYBRID TABLE HT_WEATHER ( ||   ID NUMBER(38,0) NOT NULL,                 ||   START_TIME TIMESTAMP_NTZ(9),              ||   PRECIP NUMBER(3,2),                       ||   CITY VARCHAR(20),                         ||   COUNTY VARCHAR(20),                       ||   primary key (ID)                          || );                                          |+---------------------------------------------+