Best ways to Describe Table in Vertica


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)


No comments:

Post a Comment

CONTACT

Name

Email *

Message *