HOW TO: Get DDL of database objects in Snowflake?

Spread the love

GET_DDL Function

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.

  • DATABASE
  • 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)

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’);

GET_DDL Output

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.

DDL of Customer without fully-qualified name
DDL of Customer without fully-qualified name

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.

DDL of Customer with fully-qualified name
DDL of Customer with fully-qualified name

Subscribe to our Newsletter !!

Related Articles:

  • Snowflake Resource Monitors

    Snowflake Resource Monitors assist in cost management and prevent unforeseen credit usage caused by operating warehouses.

    READ MORE

  • HOW TO: Remove Duplicates in Snowflake?

    Learn different ways to remove duplicate records from a Snowflake table using SWAP WITH and INSERT OVERWRITE commands.

    READ MORE

  • Snowflake Dynamic Data Masking

    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.

    READ MORE

Leave a Comment

Related Posts