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 -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=> \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:

\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.




Interested in working with Fahd? Schedule a tech call.

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.


Leave a Reply

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