Vertica Analytics Platform Version 9.2.x Documentation

TABLES

Provides information about all tables in the database.

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.

IS_SHARED BOOLEAN Indicates whether the table is located on shared storage.
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 table's partition expression.

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 9.2.x

1: Direct

5: Trickle

6: Auto (default)

PARTITION_GROUP_EXPRESSION VARCHAR

The expression of a GROUP BY clause that qualifies a table's partition clause.

ACTIVE_PARTITION_COUNT INTEGER Specifies a table's active partition count as set by CREATE TABLE or ALTER TABLE. If null, the table gets its active partition count from configuration parameter ActivePartitionCount. For details, see Active and Inactive Partitions in the Administrator's Guide.

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)