TABLES

Provides information about all tables in the database.

Tip: Columns TABLE_SCHEMA and TABLE_NAME are case sensitive. To query TABLES on these columns, use the case-insensitive ILIKE predicate. For example:

SELECT table_schema, table_name FROM v_catalog.tables WHERE table_schema ILIKE 'Store%';
Column Name Data Type Description
TABLE_SCHEMA_ID INTEGER

A unique numeric ID that identifies the schema and is assigned by the Vertica catalog.

TABLE_SCHEMA VARCHAR

The schema name for which information is listed.

TABLE_ID INTEGER

A unique numeric ID that identifies the table and is assigned by the Vertica catalog.

TABLE_NAME VARCHAR

The table name for which information is listed.

OWNER_ID INTEGER

A unique numeric ID that identifies the owner and is assigned by the Vertica catalog.

OWNER_NAME VARCHAR

The name of the user who created the table.

IS_TEMP_TABLE BOOLEAN

Indicates whether this table is a temporary table.

IS_SYSTEM_TABLE BOOLEAN

Indicates whether table is a system table.

FORCE_OUTER INTEGER Specifies whether this table is joined to another as an inner or outer input. For details, see Controlling Join Inputs in Analyzing Data.
IS_FLEXTABLE BOOLEAN

Indicates whether the table is a Flex table.

HAS_AGGREGATE_PROJECTION BOOLEAN

Indicates whether the table has live aggregate projections.

SYSTEM_TABLE_CREATOR VARCHAR

The name of the process that created the table, such as Designer.

PARTITION_EXPRESSION VARCHAR

The partition expression for the table.

CREATE_TIME

TIMESTAMP

Returns the timestamp, indicating when the table was created.

TABLE_DEFINITION VARCHAR

The COPY statement table definition. This column is applicable only to external tables.

RECOVER_PRIORITY INTEGER The priority rank for the table for a Recovery By Table.
STORAGE_MODE INTEGER

Specifies the load method a table uses, set to one of the following integer values:

0: The initial value for all tables that pre-date Vertica 8.1.x

1: Direct

5: Trickle

6: Auto (default)

Examples

Find when tables were created:

=> SELECT table_schema, table_name, create_time FROM tables;
 table_schema |      table_name       |          create_time
--------------+-----------------------+-------------------------------
 public       | customer_dimension    | 2011-08-15 11:18:25.784203-04
 public       | product_dimension     | 2011-08-15 11:18:25.815653-04
 public       | promotion_dimension   | 2011-08-15 11:18:25.850592-04
 public       | date_dimension        | 2011-08-15 11:18:25.892347-04
 public       | vendor_dimension      | 2011-08-15 11:18:25.942805-04
 public       | employee_dimension    | 2011-08-15 11:18:25.966985-04
 public       | shipping_dimension    | 2011-08-15 11:18:25.999394-04
 public       | warehouse_dimension   | 2011-08-15 11:18:26.461297-04
 public       | inventory_fact        | 2011-08-15 11:18:26.513525-04
 store        | store_dimension       | 2011-08-15 11:18:26.657409-04
 store        | store_sales_fact      | 2011-08-15 11:18:26.737535-04
 store        | store_orders_fact     | 2011-08-15 11:18:26.825801-04
 online_sales | online_page_dimension | 2011-08-15 11:18:27.007329-04
 online_sales | call_center_dimension | 2011-08-15 11:18:27.476844-04
 online_sales | online_sales_fact     | 2011-08-15 11:18:27.49749-04
(15 rows)

Find out whether certain tables are temporary and flex tables:

=> SELECT distinct table_name, table_schema, is_temp_table, is_flextable FROM v_catalog.tables 
   WHERE table_name ILIKE 't%';
  table_name  | table_schema | is_temp_table | is_flextable
--------------+--------------+---------------+-----------------
 t2_temp      | public       | t             | t
 tt_keys      | public       | f             | f
 t2_temp_keys | public       | f             | f
 t3           | public       | t             | f
 t1           | public       | f             | f
 t9_keys      | public       | f             | f
 t2_keys      | public       | f             | t
 t6           | public       | t             | f
 t5           | public       | f             | f
 t2           | public       | f             | t
 t8           | public       | f             | f
 t7           | public       | t             | f
 tt           | public       | t             | t
 t2_keys_keys | public       | f             | f
 t9           | public       | t             | t
(15 rows)