CODEWITHSUNDEEP
sqlvertica
Nishanth Reddy
Nishanth Reddy

Reputation: 609

Getting creation-SQL of a table in HP Vertica using a query

I am using HP Vertica db engine. There are some tables created in the database. I have a requirement wherein I need to get the create-table script of a table given the table name by querying over a system-table or a stored-proc or otherwise. Any help in reaching this need is highly appreciated. Thanks.

Upvotes: 4

Views: 5943

Answers (1)

Kermit
Kermit

Reputation: 34054

The easiest way to get the table definition for a table is by using EXPORT_TABLES(). This function allows multiple objects for the scope.

You can script the export statement and execute it inside a script, such as:

SELECT 'SELECT EXPORT_TABLES('''', ''' || table_schema || '.' || table_name || ''');' FROM v_catalog.tables;

Alternatively, you can roll up to the schema level using:

SELECT EXPORT_TABLES('', 'schema');

The difference being that EXPORT_TABLES will not produce definition for any projections associated with the table. If you need the projection with the table definition, use EXPORT_OBJECTS.

Upvotes: 11

Related Questions