describe table in vertica

How do I describe table in vertica ? I am using DB visualizer as IDE . I want to do something as 
Describe table in My-sql.

I have tried 

SELECT column_name, data_type FROM system_columns WHERE table_name like 

It does not work!! Returns 0 rows


  • It is just columns table no system columns. 
  • knicely87knicely87 Registered User
    Maybe try ilike if you are using mix case?

    dbadmin=> select column_name, data_type from system_columns where table_name ilike '%system_columns%';
           column_name        |   data_type   
     table_id                 | int
     table_schema             | varchar(128)
     table_name               | varchar(128)
     is_system_table          | boolean
     column_id                | varchar(41)
     column_name              | varchar(128)
     data_type                | varchar(128)
     data_type_id             | int
     data_type_length         | int
     character_maximum_length | int
     numeric_precision        | int
     numeric_scale            | int
     datetime_precision       | int
     interval_precision       | int
     ordinal_position         | int
     is_nullable              | boolean
     column_default           | varchar(65000)
    (17 rows)
  • Navin_CNavin_C Registered User
    If using Dbvisualizer, you can follow these steps

    Right Click on table --> Select "Script as" option --> select "CREATE" Radio option --> Create table script created for you.

    OR, you can use this query to get all details that you would get using describe table
    select distinct t.table_schema,t.table_name, c.ordinal_position, c.column_name, c.data_type, c.is_nullable , c.is_identity, con.constraint_name,con.constraint_type  from  columns c   left join tables t on t.table_schema = c.table_schema and t.table_name = 'table_name'  left join constraint_columns con on t.table_name = con.table_name and c.column_name = con.column_name and constraint_type='p'  where c.table_schema = 'schema_name' and c.table_name = 'table_name'  order by ordinal_position
    Hope this helps
  • Abhishek_RanaAbhishek_Rana Employee, Registered User, VerticaExpert
    An easy & one more way to do it in VSQL is :

    \d <table_name>
  • SaifMasadehSaifMasadeh Registered User

    Use the command below :


    \d table_name


    Schema |     Table      |        Column        |     Type      | Size |             Default              | Not Null | Primary Key | Foreign Key

  • virendharvirendhar Registered User
    edited March 8

    \d table_name
    \dv view_name
    \dt table_name --> table high level details

  • sheethalsheethal Registered User

    Adds or drops a single column or a table constraint from the metadata of a table.
    ALTER TABLE table-name
    ADD COLUMN column-definition
    | ADD table-constraint
    | DROP CONSTRAINT constraint-name
    python training

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file