Generate Snowflake Objects DDL using GET_DDL Function

Snowflake is a fully managed cloud data warehouse solution provided on AWS, Azure and GCP. You don’t have to manage hardware’s and your only task is to manage databases and tables that you create as part of your project development. In this article, we will check one of the administrative tasks, generate DDL for Snowflake objects such as view, tables DDL using built-in GET_DDL function.

Snowflake Objects DDL using GET_DDL Function

Snowflake provides many useful functions to make developers and administrators task easy. One of such function is GET_DDL function, which allows you to get the objects DDL from the specific database.

Snowflake GET_DDL Function

The Snowflake GET_DDL function 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.

Snowflake GET_DDL Syntax

Following is the syntax of DDL generation function available in Snowflake.

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

Where, object_type specifies the type of object for which the DDL is returned. Following are the valid object types.

DATABASE
SCHEMA
TABLE
VIEW – includes materialized views
STREAM
SEQUENCE
FILE_FORMAT
PIPE
FUNCTION – User Defined Functions
PROCEDURE – stored procedures

and object_name is the name of an object. For example, table1, test_table, etc.

Snowflake GET_DDL Return Type

The GET_DDL function in Snowflake returns a string (VARCHAR) containing the text of the DDL statement that created the object.

Snowflake GET_DDL Examples

Following is the example to extract table DDL from S_STUDENT database.

select GET_DDL('table', 'S_STUDENT');
+-------------------------------------+
| GET_DDL('TABLE', 'S_STUDENT')       |
|-------------------------------------|
| create or replace TABLE S_STUDENT ( |
|       ID NUMBER(38,0),              |
|       NAME VARCHAR(200),            |
|       CITY VARCHAR(100)             |
| );                                  |
+-------------------------------------+

Snowflake Generate DDL for all Objects in Database

You can use the GET_DDL function to generate DDL for all objects such as tables, views, functions, procedures, etc.

For example, generate DDL for all objects present in TEST_DB.

SELECT GET_DDL('DATABASE', 'TEST_DB');
+--------------------------------- +
| GET_DDL('DATABASE', 'TEST_DB') |
|------------------------------------ |
| create or replace database TEST_DB; |
| |
| create or replace schema PUBLIC; |
| |
| create or replace TABLE S_STUDENT ( |
|       ID NUMBER(38,0), |
|       NAME VARCHAR(200), |
|       CITY VARCHAR(100) |
| ); |
| |
| create view std_view as select * from S_STUDENT; |
| |
| CREATE OR REPLACE PROCEDURE "GETROWCOUNT" (TABLE_NAME VARCHAR) |
| RETURNS FLOAT |
| LANGUAGE JAVASCRIPT |
.
.
.

Export Snowflake Database Objects DDL to Text File

You can export database objects such as table, view, function, procedure, materialized view, its DDL to the output to text file. You can use Snowsql to export get_ddl function results to text file.

For more information, follow my other article, Export Snowflake Table Data to Local CSV format.