Magic of “\d” in Vertica

Posted in: Big Data, Technical Track

A quick neat way to list down important and oft-needed information like names of databases, schemas, users, tables, projections etc. We can also use patterns with the ‘\d’ to narrow down the results. Let’s see it in action:

Connect with Vertica vsql:

vsql  -U dbadmin -w vtest -h 0.0.0.0 -p 5433 -d vtest
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type:  \h or \? for help with vsql commands
\g or terminate with semicolon to execute query
\q to quit

vtest=>
vtest=> \dn
List of schemas
Name     |  Owner  | Comment
————–+———+———
v_internal   | dbadmin |
v_catalog    | dbadmin |
v_monitor    | dbadmin |
public       | dbadmin |
TxtIndex     | dbadmin |
store        | dbadmin |
online_sales | dbadmin |
mytest       | mytest  |
(8 rows)

vtest=> \dn mytest
List of schemas
Name  | Owner  | Comment
——–+——–+———
mytest | mytest |
(1 row)

vtest=> \dn my*
List of schemas
Name  | Owner  | Comment
——–+——–+———
mytest | mytest |
(1 row)

vtest=> \dn v
List of schemas
Name | Owner | Comment
——+——-+———
(0 rows)

vtest=> \dn *v*
List of schemas
Name    |  Owner  | Comment
————+———+———
v_internal | dbadmin |
v_catalog  | dbadmin |
v_monitor  | dbadmin |
(3 rows)

Likewise you can list down other information like:

vtest=> \dj
List of projections
Schema    |            Name             |  Owner  |       Node       | Comment
————–+—————————–+———+——————+———
mytest       | ptest                       | mytest  | v_vtest_node0002 |
mytest       | testtab_super               | mytest  |                  |

To list down views:

vtest=> \dv
No relations found.

If you connect with the mytest user and run:

vtest=> \dt
List of tables
Schema |  Name   | Kind  | Owner  | Comment
——–+———+——-+——–+———
mytest | testtab | table | mytest |
(1 row)

Following are more ‘\d’ options from help:

Informational
\d [PATTERN]   describe tables (list tables if no argument is supplied)
PATTERN may include system schema name, e.g. v_catalog.*
\df [PATTERN]  list functions
\dj [PATTERN]  list projections
\dn [PATTERN]  list schemas
\dp [PATTERN]  list table access privileges
\ds [PATTERN]  list sequences
\dS [PATTERN]  list system tables. PATTERN may include system schema name
such as v_catalog, v_monitor, or v_internal.
Example: v_catalog.a*
\dt [PATTERN]  list tables
\dtv [PATTERN] list tables and views
\dT [PATTERN]  list data types
\du [PATTERN]  list users
\dv [PATTERN]  list views

 

Discover more about our expertise in Big Data.

 

email

Author

Want to talk with an expert? Schedule a call with our team to get the conversation started.

About the Author

I have been in love with Oracle blogging since 2007. This blogging, coupled with extensive participation in Oracle forums, plus Oracle related speaking engagements, various Oracle certifications, teaching, and working in the trenches with Oracle technologies has enabled me to receive the Oracle ACE award. I was the first ever Pakistani to get that award. From Oracle Open World SF to Foresight 20:20 Perth. I have been expressing my love for Exadata. For the last few years, I am loving the data at Pythian, and proudly writing their log buffer carnivals.

1 Comment. Leave new

Thanks Fahd, very well explained with lots of options.

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *