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
describe table in vertica
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)
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)
+2If 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
NC
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_positionHope this helps
NC
An easy & one more way to do it in VSQL is :
\d <table_name>
\d <table_name>
Use the command below :
\d table_name
Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+----------------+----------------------+---------------+------+----------------------------------+----------+-------------+-------------
\d table_name
\dv view_name
\dt table_name --> table high level details
Sign up
Already have an account? Login
Welcome to the Rocket Forum!
Please log in or register:
Employee Login | Registration Member Login | RegistrationEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.
77 4th Avenue
Waltham, MA 02451 USA
