DUMP DATABASE DDL

DUMP_DATABASE_DDL() is a function that shows the CREATE statements for database objects including views and tables.

Syntax

SELECT DUMP_DATABASE_DDL()

Examples

SELECT
  DUMP_DATABASE_DDL();

Exporting database DDL to a file

COPY
  (
    SELECT
      DUMP_DATABASE_DDL()
  ) TO
WRAPPER
  csv_fdw
OPTIONS
  (LOCATION = 's3://sqream-docs/database.ddl');

Showing the CREATE Statements for UDFs

DUMP_DATABASE_DDL does not show UDFs.

To list available UDFs:

  1. Retrieve UDFs from catalog:

    SELECT
      *
    FROM
      sqream_catalog.user_defined_functions;
    

    Output:

    database_name|function_id|function_name|
    -------------+-----------+-------------+
    master       |          0|add_months   |
    master       |          2|my_distance  |
    
  2. Export UDFs one-by-one using GET_FUNCTION_DDL.

Permissions

The role must have the CONNECT permission at the database level.