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. In Eon Mode, temporary tables are never shared. If |
| 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 |
Deprecated, always set to DIRECT. |
| PARTITION_GROUP_EXPRESSION | VARCHAR |
The expression of a |
| 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. |
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)