TABLES

Provides information about all tables in the database.

Provides information about all tables in the database.

The TABLE_SCHEMA and TABLE_NAME columns are case-sensitive. To restrict a query based on those 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 Whether this table is a temporary table.
IS_SYSTEM_TABLE BOOLEAN Whether this table is a system table.
FORCE_OUTER INTEGER Whether this table is joined to another as an inner or outer input. For details, see Controlling join inputs.
IS_FLEXTABLE BOOLEAN Whether the table is a Flex table.
IS_SHARED BOOLEAN Whether the table is located on shared storage. Not used for temporary tables in Eon Mode.
HAS_AGGREGATE_PROJECTION BOOLEAN 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 When the table was created.
TABLE_DEFINITION VARCHAR For external tables, the COPY FROM portion of the table definition.
RECOVER_PRIORITY INTEGER The priority rank for the table for a Recovery By Table.
STORAGE_MODE INTEGER Deprecated, always set to DIRECT.
PARTITION_GROUP_EXPRESSION VARCHAR The expression of a GROUP BY clause that qualifies a table's partition clause.
ACTIVE_PARTITION_COUNT INTEGER The table's active partition count as set by CREATE TABLE or ALTER TABLE. If null, the table gets its active partition count from the ActivePartitionCount configuration parameter. For details, see Active and inactive partitions.
IS_MERGEOUT_ENABLED BOOLEAN Whether mergeout is enabled (t) or disabled (f) on ROS containers that consolidate projection data of this table. By default, mergeout is enabled on all tables. You can disable mergeout on a table with ALTER TABLE. For details, see Disabling mergeout on specific tables.
IMMUTABLE_ROWS_SINCE_TIMESTAMP TIMESTAMPTZ Set only for immutable tables, the server system time when immutability was applied to this table. This value can help with long-term timestamp retrieval and efficient comparison.
IMMUTABLE_ROWS_SINCE_EPOCH INTEGER Set only for immutable tables, the epoch that was current when immutability was applied. This setting can help protect the table from attempts to pre-insert records with a future timestamp, so that row's epoch is less than the table's immutability epoch.
IS_EXTERNAL_ICEBERG_TABLE BOOLEAN Whether this table is an Iceberg table.

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)