Snowflake provides GET_DDL Function using which DDL of database objects can be extracted. The GET_DDL function returns a DDL statement as output that can be used to create the database objects.
GET_DDL function takes object type and object name as input to generate the DDL of the required object.
Syntax of GET_DDL Function
The syntax of GET_DDL function in snowflake is as shown below
GET_DDL( '<object_type>' , '[<namespace>.]<object_name>' )
The object_type argument specifies the type of the object for which DDL is required. Below are the valid object types that can be passed to the GET_DDL function.
- FUNCTION (for UDFs, including external functions)
- POLICY (masking and row access policies)
- PROCEDURE (for stored procedures)
- TABLE (including for external tables)
- TAG (object tagging)
- VIEW (including for materialized views)
The object_name argument specifies the name of the object for which DDL is required.
The namespace argument is the database and/or schema in which the object resides which is optional if database and schema are in use in current session in the worksheet.
Triggering GET_DDL function to extract DDL
Trigger the GET_DDL function to extract the DDL of a Snowflake object as shown below.
SELECT GET_DDL('<object_type>' , '[<namespace>.]<object_name>');
The below example extracts the DDL of a table named CUSTOMER.
SELECT GET_DDL(‘table’, ‘customer');
If the database and schema are not in use, you can pass the complete namespace to extract DDL as shown below.
SELECT GET_DDL(‘table’, ‘mydb.sales.customer');
Extract DDL of all objects in a database using GET_DDL
For databases and schemas, GET_DDL is recursive i.e. it returns the DDL statements of all supported objects within the specified database/schema.
The below example extracts the DDL of all supported objects present in the database my_db.
SELECT GET_DDL(‘database, ‘my_db’);
The below example extracts the DDL of all supported objects present in the schema my_schema.
SELECT GET_DDL(‘schema, ‘my_db.my_schema’);
By default the DDL statement returned by GET_DDL function do not use a fully-qualified named of the object.
The below example shows the output of query providing the DDL of customer table without database and schema information.
To return the DDL with fully-qualified named, use the true indicator in the syntax of GET_DDL function as shown below.
GET_DDL( '<object_type>' , '[<namespace>.]<object_name>', true )
The below example shows the output of query providing the DDL of customer table with a fully-qualified named providing the database and schema information.
Subscribe to our Newsletter !!
Snowflake Resource Monitors assist in cost management and prevent unforeseen credit usage caused by operating warehouses.
Learn different ways to remove duplicate records from a Snowflake table using SWAP WITH and INSERT OVERWRITE commands.
Snowflake Dynamic Data Masking is a security feature that allows you to mask sensitive data in database tables, views, and query results in real-time.