EXPORT_TABLES
Generates a SQL script that can be used to recreate a logical schema—schemas, tables, constraints, and views—on another cluster. EXPORT_OBJECTS
only exports objects to which the user has access.
Syntax
EXPORT_TABLES( '[destination]' [, 'scope' ] )
Parameters
destination |
Specifies where to send output, one of the following:
|
scope |
Specifies one or more tables to export, as follows: [database.]schema[.table][,…] If set to an empty string, Vertica exports all non-virtual table objects to which you have access, including table schemas, sequences, and constraints. If you specify a schema, Vertica exports all non-virtual table objects in that schema. If you specify a database, it must be the current database. |
Privileges
None
Example
See Exporting Tables.
The following example exports to standard output all tables in the store
schema:
=> SELECT EXPORT_TABLES('','store'); EXPORT_TABLES --------------------------------------------------------------------------------------------------- CREATE SCHEMA store; CREATE TABLE store.store_dimension ( store_key int NOT NULL, store_name varchar(64), store_number int, store_address varchar(256), store_city varchar(64), store_state char(2), store_region varchar(64), floor_plan_type varchar(32), photo_processing_type varchar(32), financial_service_type varchar(32), selling_square_footage int, total_square_footage int, first_open_date date, last_remodel_date date, number_of_employees int, annual_shrinkage int, foot_traffic int, monthly_rent_cost int, CONSTRAINT C_PRIMARY PRIMARY KEY (store_key) DISABLED ); CREATE TABLE store.store_sales_fact ( date_key int NOT NULL, product_key int NOT NULL, product_version int NOT NULL, store_key int NOT NULL, promotion_key int NOT NULL, customer_key int NOT NULL, employee_key int NOT NULL, pos_transaction_number int NOT NULL, sales_quantity int, sales_dollar_amount int, cost_dollar_amount int, gross_profit_dollar_amount int, transaction_type varchar(16), transaction_time time, tender_type varchar(8) ); CREATE TABLE store.store_orders_fact ( product_key int NOT NULL, product_version int NOT NULL, store_key int NOT NULL, vendor_key int NOT NULL, employee_key int NOT NULL, order_number int NOT NULL, date_ordered date, date_shipped date, expected_delivery_date date, date_delivered date, quantity_ordered int, quantity_delivered int, shipper_name varchar(32), unit_price int, shipping_cost int, total_order_cost int, quantity_in_stock int, reorder_level int, overstock_ceiling int ); ALTER TABLE store.store_sales_fact ADD CONSTRAINT fk_store_sales_date FOREIGN KEY (date_key) references public.date_dimension (date_key); ALTER TABLE store.store_sales_fact ADD CONSTRAINT fk_store_sales_product FOREIGN KEY (product_key, product_version) references public.product_dimension (product_key, product_version); ALTER TABLE store.store_sales_fact ADD CONSTRAINT fk_store_sales_store FOREIGN KEY (store_key) references store.store_dimension (store_key); ALTER TABLE store.store_sales_fact ADD CONSTRAINT fk_store_sales_promotion FOREIGN KEY (promotion_key) references public.promotion_dimension (promotion_key); ALTER TABLE store.store_sales_fact ADD CONSTRAINT fk_store_sales_customer FOREIGN KEY (customer_key) references public.customer_dimension (customer_key); ALTER TABLE store.store_sales_fact ADD CONSTRAINT fk_store_sales_employee FOREIGN KEY (employee_key) references public.employee_dimension (employee_key); ALTER TABLE store.store_orders_fact ADD CONSTRAINT fk_store_orders_product FOREIGN KEY (product_key, product_version) references public.product_dimension (product_key, product_version); ALTER TABLE store.store_orders_fact ADD CONSTRAINT fk_store_orders_store FOREIGN KEY (store_key) references store.store_dimension (store_key); ALTER TABLE store.store_orders_fact ADD CONSTRAINT fk_store_orders_vendor FOREIGN KEY (vendor_key) references public.vendor_dimension (vendor_key); ALTER TABLE store.store_orders_fact ADD CONSTRAINT fk_store_orders_employee FOREIGN KEY (employee_key) references public.employee_dimension (employee_key); (1 row)