GENERATE_DDL_FOR_SCHEMA

Returns the create statement for every item in a named schema.

Syntax

[SYS_BOOT.MGMT.]GENERATE_DDL_FOR_SCHEMA(<catalogname>,<schema_name>);

where catalogname (optional) is the name of the catalog and schema_name (mandatory) is the name of the schema.

Example

Here are the first lines generated for the StreamLab Sydney Buses Gallery app:

SELECT STATEMENT FROM TABLE(SYS_BOOT.MGMT.GENERATE_DDL_FOR_SCHEMA('LOCALDB','StreamLab_Output_buses'));

'STATEMENT'
'CREATE OR REPLACE SCHEMA "StreamLab_Output_buses";'
''
'CREATE OR REPLACE FUNCTION "StreamLab_Output_buses"."pipeline_1_step_1_parser"('
'  INPUT CURSOR,'
'  COLUMNNAME COLUMN_LIST,'
'  PARSERCLASSNAME VARCHAR(256),'
'  OPTIONS CURSOR)'
'RETURNS TABLE ('
'   INPUT.*,'
'   "bearing" INTEGER,'
'   "lon" DOUBLE,'
'   "lat" DOUBLE'
')'
'SPECIFIC "StreamLab_Output_buses"."pipeline_1_step_1_parser"'
'LANGUAGE JAVA'
'PARAMETER STYLE SYSTEM DEFINED JAVA'
'NO SQL'
'NOT DETERMINISTIC'
'EXTERNAL NAME 'class com.sqlstream.aspen.namespace.common.ParserUdx.parseColumn';'
''
'CREATE OR REPLACE FUNCTION "StreamLab_Output_buses"."source_1_throttlefunc"('
'  INPUTROWS CURSOR,'
'  THROTTLESCALE INTEGER)'
'RETURNS TABLE ('
'   INPUTROWS.*'
')'
'SPECIFIC "StreamLab_Output_buses"."source_1_throttlefunc"'
'LANGUAGE JAVA'
'PARAMETER STYLE SYSTEM DEFINED JAVA'
'NO SQL'
'NOT DETERMINISTIC'
'EXTERNAL NAME 'class:com.sqlstream.plugin.timesync.ThrottleStream.throttle';'
''
'CREATE FOREIGN TABLE "StreamLab_Output_buses"."external_table_1" ('
'   "id" INTEGER,'
'   "name" VARCHAR(128)'
')'
'SERVER "PostgreSQL_DB_1_buses"'
'OPTIONS ('
'  SCHEMA_NAME 'public','
'  TABLE_NAME 'drivers''
');'
''
'CREATE FOREIGN TABLE "StreamLab_Output_buses"."external_table_out_1" ('
'   "position_time" TIMESTAMP,'
'   "bus" VARCHAR(128),'
'   "driver_no" INTEGER,'
'   "speed" DOUBLE'
')'
'SERVER "PostgreSQL_DB_1_buses"'
'OPTIONS ('
'  SCHEMA_NAME 'public','
'  TABLE_NAME 'speeders','
'  TRANSACTION_ROWTIME_LIMIT '1000','
'  TRANSACTION_ROW_LIMIT '0''
');'
''
... etc