Best ways to Describe Table in Vertica

July 9, 2018
There are various ways to describe table in vertica, among some of them are listed below.

describe table

List table definition using d:

=>  d table_name;
=> d schema_name.table_name;

It will list all the below details:
Schema
Table
Column
Type
Size
Default
Not Null
Primary Key
Foreign Key

oraadmin=> d P1_orahow.u_acct_trans;
List of Fields by Tables            

  Schema  |        Table |         Column         |     Type    
----------+----------------------+------------------------+---------

 P1_orahow | u_acct_trans| act_id                 | numeric(19,0)
 P1_orahow | u_acct_trans| sfl_id                 | int          
 P1_orahow | u_acct_trans| sts_id                 | int          
 P1_orahow | u_acct_trans| act_record_number      | int          
 P1_orahow | u_acct_trans| act_record_address     | numeric(19,0)
 P1_orahow | u_acct_trans| act_record_length      | int          
 P1_orahow | u_acct_trans| act_record_type        | varchar(255)
 P1_orahow | u_acct_trans| act_duplicate_hashcode | int          
 P1_orahow | u_acct_trans| act_dup_fl             | char(1)      
 P1_orahow | u_acct_trans| act_filename           | varchar(255)
 P1_orahow | u_acct_trans| act_cust_id            | varchar(255)
 P1_orahow | u_acct_trans| act_acct_id            | varchar(255)
 P1_orahow | u_acct_trans| act_subs_id            | numeric(19,0)
 P1_orahow | u_acct_trans| act_re_name            | varchar(255)
 P1_orahow | u_acct_trans| act_item_type_name     | varchar(255)
 P1_orahow | u_acct_trans| act_item_type_code     | varchar(255)
 P1_orahow | u_acct_trans| act_event_time         | timestamp    
 P1_orahow | u_acct_trans| act_charge             | numeric(19,0)
 P1_orahow | u_acct_trans| act_price_plan_code    | varchar(255)
 P1_orahow | u_acct_trans| act_price_plan_name    | varchar(255)
 P1_orahow | u_acct_trans| act_event_inst_id      | varchar(255)
 P1_orahow | u_acct_trans| act_msisdn             | varchar(255)

List high level Table definition:

oraadmin=> dt u_acct_trans_log;

                          List of tables

    Schema     |         Name         | Kind  |  Owner   | Comment
---------------+----------------------+-------+----------+---------
 P1_orahow      | u_acct_trans | table | oraadmin  |
 P1_orahow_test | u_acct_trans | table | ora_test |

(2 rows)

Describe from v_catalog.columns definition:

SELECT *
FROM   v_catalog.columns
WHERE  table_schema='P1_orahow'
       AND table_name='u_acct_trans'
ORDER  BY ordinal_position;

List table definition using table export:

oraadmin=> SELECT EXPORT_TABLES('', 'schema_name.table_name');
oraadmin=> SELECT EXPORT_TABLES('', 'P1_orahow.u_acct_trans');

List table definition by table export, which will give you the full create statement, including projections:

oraadmin=> SELECT export_objects('', 'schema_name.table_name');
oraadmin=> SELECT export_objects('', 'P1_orahow.u_acct_trans');

List all tables in Public Schema:

oraadmin-> dt public.*;
                        List of tables

 Schema |           Name           | Kind  |  Owner  | Comment

--------+--------------------------+-------+---------+---------

 public | abc                      | table | oraadmin |
 public | cdr_offpeak_ts_temp      | table | oraadmin |
 public | cdr_percall_ts_temp      | table | oraadmin |
 public | gprs_ts_temp             | table | oraadmin |
 public | ipdr_ts_temp             | table | oraadmin |
 public | nik_cdr_offpeak_summary  | table | oraadmin |
 public | nik_cdr_per_call_summary | table | oraadmin |
 public | nik_gprs_cdr_summary     | table | oraadmin |
 public | nik_ipdr_summary         | table | oraadmin |
 public | nik_recharge_log_summary | table | oraadmin |
 public | recharge_log_ts_temp     | table | oraadmin |

(11 rows)

Must Read: