TABLES

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_NAMESPACE_NAME	VARCHAR	For Eon Mode databases, name of the namespace to which the table belongs.
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)