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.
`IS_FLEXTABLE`	BOOLEAN	Indicates whether the table is a Flex table.
`IS_SHARED`	BOOLEAN	Indicates whether the table is located on shared storage. Note In Eon Mode, temporary tables are never shared. If `IS_TEMP_TABLE` is set to true, Vertica ignores `IS_SHARED` settings.
`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	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	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.
`IS_MERGEOUT_ENABLED`	BOOLEAN	Specifies 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.

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)