- Categories:
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:
Alerts (see CREATE ALERT)
Databases (see CREATE DATABASE)
Event tables (see CREATE EVENT TABLE)
External tables (see CREATE EXTERNAL TABLE)
File formats (see CREATE FILE FORMAT)
Pipes (see CREATE PIPE)
Policies (see CREATE MASKING POLICY and CREATE ROW ACCESS POLICY)
Schemas (see CREATE SCHEMA)
Sequences (see CREATE SEQUENCE)
Stored procedures (see CREATE PROCEDURE)
Streams (see CREATE STREAM)
Tables (see CREATE TABLE)
Tags (see CREATE TAG)
Tasks (see CREATE TASK)
UDFs, including external functions (see CREATE FUNCTION)
Views (see CREATE VIEW)
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
EVENT_TABLE
FILE_FORMAT
FUNCTION (for UDFs, including external functions)
PIPE
POLICY (masking and row access policies)
PROCEDURE (for stored procedures)
SCHEMA
SEQUENCE
STREAM
TABLE (including for external tables)
TAG (object tagging)
TASK
VIEW (including for materialized views)
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, tasks, sequences, file formats, pipes, policies, and UDFs), takes the form of
database.schema
orschema
.
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_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 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¶
The following notes apply to all supported objects:
object_type
andobject_name
(includingnamespace
if specified) must be enclosed in single quotes.For
object_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 for the view and vice-versa.If
object_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.If
object_type
isPROCEDURE
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 [ , ... ] ] )'
.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).
The following notes are specific to view objects:
The query result always:
Returns lowercase SQL text for
create or replace view
, even if the casing in the original SQL statement used to create the view 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 theCOPY 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 (i.e.
AS
). For example, in the following code, the comment immediately prior to the AS clause is removed:create view view_t1 -- GET_DDL() removes this comment. AS select * from t1;
The following notes apply specifically to table and view objects with a tag, masking policy, or row access policy:
The role executing the GET_DDL query must have the global APPLY MASKING POLICY, APPLY ROW ACCESS 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 text indicates 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 removed prior 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 administrator to determine which policies and tags are necessary for the columns or object. Finally, edit the GET_DDL query result and then recreate the object.
Without the mentioned privileges, this table function does not return the corresponding row for the policy and tag assignments in the output 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 REPLACE statement.
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 assignments using 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.
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; | ---------------------------------------------------------------------------+